php dropdown not populating from mysql db

Hi, I know this has been asked a “million times” and I’ve looked at: https://secure.php.net/manual/en/mysqli-result.fetch-array.php and html - PHP/MySQL Dropdown - Stack Overflow for assistance. Despite looking over my code carefully, I’m still not getting any results.

I do have a dropdown that seems to have the right number of “options” in the dropdown but there’s no text in the blanks. Here’s my code snippet:
[php]Category

<?php require_once 'philobytes_login.php'; //servername //dbname //username //password // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $query = "SELECT ad_category_id, ad_category_parent_id, b.category_name from wp_awpcp_ads a INNER JOIN wp_awpcp_categories b where ad_category_id = category_id"; $select_category = mysqli_query($conn, $query); echo ""; echo "-Select-"; while ($row = mysqli_fetch_array($select_category)) { echo "" .$row['b.category_id'].""; } $conn->close(); echo ""; ?>[/php]

and here’s the full code:
[php]<?php
?>

<title>First Form</title>

Add Your Listing

Name *

Email
*

Phone
*

Category

<?php require_once 'philobytes_login.php'; //servername //dbname //username //password // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $query = "SELECT ad_category_id, ad_category_parent_id, b.category_name from wp_awpcp_ads a INNER JOIN wp_awpcp_categories b where ad_category_id = category_id"; $select_category = mysqli_query($conn, $query); echo ""; echo "-Select-"; while ($row = mysqli_fetch_array($select_category)) { echo "" .$row['b.category_id'].""; } $conn->close(); echo ""; ?>


Please add a 2 word Title for the printed ad


Ad Title

Ad Details

(18 words left)

<?php /** * This function can be used to check the sanity of variables * * [member=5926]access[/member] private * * @param string $type The type of variable can be bool, float, numeric, string, array, or object * @param string $string The variable name you would like to check * @param string $length The maximum length of the variable * * return bool */ function sanityCheck($string, $type, $length){ // assign the type $type = 'is_'.$type; if(!$type($string)) { return FALSE; } // now we see if there is anything in the string elseif(empty($string)) { return FALSE; } // then we check how long the string is elseif(strlen($string) > $length) { return FALSE; } else { // if all is well, we return TRUE return TRUE; } } /** * This function if the $_POST vars are set * * [member=5926]access[/member] private * * return bool */ function checkSet(){ return isset($_POST['name'], $_POST['phone'], $_POST['email']); } /** * This function checks a number is greater than zero * and exactly $length digits. returns TRUE on success. * * [member=5926]access[/member] private * * @param int $num The number to check * @param int $length The number of digits in the number * * return bool */ function checkNumber($num, $length){ if($num > 0 && strlen($num) == $length) { return TRUE; } } /** * This function checks if an email address in a valid format * * [member=5926]access[/member] private * * @param string $email The email address to check * * return bool */ function checkEmail($email){ return preg_match('/^\S+@[\w\d.-]{2,}\.[\w]{2,6}$/iU', $email) ? TRUE : FALSE; } // check all our variables are set if(checkSet() != FALSE) { // check the POST variable userName is sane, and is not empty if(empty($_POST['name'])==FALSE && sanityCheck($_POST['name'], 'string', 50) != FALSE) { $name = $_POST['name']; } else { echo 'Name is not set'; exit(); } // check number is greater than 0 and $length digits long if(sanityCheck($_POST['phone'], 'numeric', 7) != FALSE && checkNumber($_POST['phone'], 10) == TRUE) { $phone = preg_replace("|(^[0-9]|", "", $phone); $phone = $_POST['phone']; } else { $phone=''; } // if the checks are ok for the email we assign the email address to a variable if(sanityCheck($_POST['email'], 'string', 50) != FALSE && checkEmail($_POST['email']) != FALSE) { $email = $_POST['email']; } else // if all is not well we echo an error message echo 'Invalid Email Address Supplied'; // and exit the script exit(); } else { // this will be the default message if the form accessed without POSTing echo '

Please fill in the form above

'; } require_once 'philobytes_login.php'; //servername //dbname //username //password // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } //---------------------------------------------------- //today is Tuesday if (2 == date('N')){ $tuesday = time('12:00:00'); }else{ $tuesday = strtotime('last Tuesday'); } $tuesday = strtotime('+2 week', $tuesday); //echo date('d-m-Y', $tuesday) . '
'; $tuesday = date("Y-m-d H:i:s", $tuesday); //---------------------------------------------------- if(isset($_REQUEST['submit_ad'])) { // prepare and bind $stmt = $conn->prepare("INSERT INTO wp_awpcp_ads ( ad_contact_name, ad_contact_email, ad_contact_phone, ad_category_id, ad_title, ad_details, disabled, disabled_date, ad_postdate, ad_startdate, ad_enddate ) VALUES (?,?,?,?,?,?,1,now(),now(),now(),'".$tuesday."')"); $stmt->bind_param("ssssss", $name,$phone,$email,$category,$adtitle,$addetails); $name = $_POST['name']; $email = $_POST['email']; $phone = $_POST['phone']; $category = $_POST['category']; $adtitle = $_POST['adtitle']; $addetails = $_POST['addetails']; $stmt->execute(); echo "New ads created successfully"; } $conn->close(); ?> [/php]

and I’ve included a screenshot.

Also, as a follow-up I know I only have two items in my dropdown, but for the actual item to be reinserted I’ll need an array of three items into the larger table set. can someone help me figure out how to do that (I have no clue where to start)


Untitled.png

Turn on all error reporting in your dev environment, you could add this to the beginning of your script:

[php]error_reporting(E_ALL);
ini_set(‘display_errors’, 1)[/php]

[hr]

[php]$query = “SELECT ad_category_id, ad_category_parent_id, b.category_name from wp_awpcp_ads a INNER JOIN wp_awpcp_categories b where ad_category_id = category_id”;

// …

 echo "<option value='".$row['b.category_id']."'>" .$row['b.category_id']."</option>";[/php]

You never select “b.category_id” and will probably get an undefined index notice when trying to use it.

I guess you want something like this:

[php]echo “<option value=’”.$row[‘ad_category_id’]."’>" .$row[‘b.category_name’]."";[/php]

oops, thanks. so I swapped the code to the correct fields and added the errors code and added the ad_category_parent_id now to the added tables. here’s my updated code:[php]<?php
?>

<title>First Form</title>

Add Your Listing

Name *

Email
*

Phone
*

Category

<?php require_once 'philobytes_login.php'; //servername //dbname //username //password // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } error_reporting(E_ALL); ini_set('display_errors', 1); $query = "SELECT ad_category_id, ad_category_parent_id, b.category_name from wp_awpcp_ads a INNER JOIN wp_awpcp_categories b where ad_category_id = category_id"; $select_category = mysqli_query($conn, $query); echo ""; echo "-Select-"; while ($row = mysqli_fetch_array($select_category)) { echo "" .$row['b.category_name'].""; } $conn->close(); echo ""; ?>


Please add a 2 word Title for the printed ad


Ad Title

Ad Details

(18 words left)

<?php /** * This function can be used to check the sanity of variables * * [member=5926]access[/member] private * * @param string $type The type of variable can be bool, float, numeric, string, array, or object * @param string $string The variable name you would like to check * @param string $length The maximum length of the variable * * return bool */ function sanityCheck($string, $type, $length){ // assign the type $type = 'is_'.$type; if(!$type($string)) { return FALSE; } // now we see if there is anything in the string elseif(empty($string)) { return FALSE; } // then we check how long the string is elseif(strlen($string) > $length) { return FALSE; } else { // if all is well, we return TRUE return TRUE; } } /** * This function if the $_POST vars are set * * [member=5926]access[/member] private * * return bool */ function checkSet(){ return isset($_POST['name'], $_POST['phone'], $_POST['email']); } /** * This function checks a number is greater than zero * and exactly $length digits. returns TRUE on success. * * [member=5926]access[/member] private * * @param int $num The number to check * @param int $length The number of digits in the number * * return bool */ function checkNumber($num, $length){ if($num > 0 && strlen($num) == $length) { return TRUE; } } /** * This function checks if an email address in a valid format * * [member=5926]access[/member] private * * @param string $email The email address to check * * return bool */ function checkEmail($email){ return preg_match('/^\S+@[\w\d.-]{2,}\.[\w]{2,6}$/iU', $email) ? TRUE : FALSE; } // check all our variables are set if(checkSet() != FALSE) { // check the POST variable userName is sane, and is not empty if(empty($_POST['name'])==FALSE && sanityCheck($_POST['name'], 'string', 50) != FALSE) { $name = $_POST['name']; } else { echo 'Name is not set'; exit(); } // check number is greater than 0 and $length digits long if(sanityCheck($_POST['phone'], 'numeric', 7) != FALSE && checkNumber($_POST['phone'], 10) == TRUE) { $phone = preg_replace("|(^[0-9]|", "", $phone); $phone = $_POST['phone']; } else { $phone=''; } // if the checks are ok for the email we assign the email address to a variable if(sanityCheck($_POST['email'], 'string', 50) != FALSE && checkEmail($_POST['email']) != FALSE) { $email = $_POST['email']; } else // if all is not well we echo an error message echo 'Invalid Email Address Supplied'; // and exit the script exit(); } else { // this will be the default message if the form accessed without POSTing echo '

Please fill in the form above

'; } require_once 'philobytes_login.php'; //servername //dbname //username //password // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } //---------------------------------------------------- //today is Tuesday if (2 == date('N')){ $tuesday = time('12:00:00'); }else{ $tuesday = strtotime('last Tuesday'); } $tuesday = strtotime('+2 week', $tuesday); //echo date('d-m-Y', $tuesday) . '
'; $tuesday = date("Y-m-d H:i:s", $tuesday); //---------------------------------------------------- if(isset($_REQUEST['submit_ad'])) { // prepare and bind $stmt = $conn->prepare("INSERT INTO wp_awpcp_ads ( ad_contact_name, ad_contact_email, ad_contact_phone, ad_category_id, ad_title, ad_details, disabled, disabled_date, ad_postdate, ad_startdate, ad_enddate ) VALUES (?,?,?,?,?,?,1,now(),now(),now(),'".$tuesday."')"); $stmt->bind_param("ssssss", $name,$phone,$email,$category,$adtitle,$addetails); $name = $_POST['name']; $email = $_POST['email']; $phone = $_POST['phone']; $category = $_POST['category']; $adtitle = $_POST['adtitle']; $addetails = $_POST['addetails']; $stmt->execute(); echo "New ads created successfully"; } $conn->close(); ?> [/php]

It’s enough to add the relevant code :slight_smile:

Does it work?

If not:
Do you get any errors? What does the output look like? (view source -> copy paste the html for the select)

hmm ok, learned something new so here’s my output in the source:

Category -Select-
Notice: Undefined index: b.category_name in /home/philobytes/www/webdesign/ccshppr/wordpress/admin_insert_new.php on line 89

Notice: Undefined index: b.category_name in /home/philobytes/www/webdesign/ccshppr/wordpress/admin_insert_new.php on line 89

Notice: Undefined index: b.category_name in /home/philobytes/www/webdesign/ccshppr/wordpress/admin_insert_new.php on line 89

Notice: Undefined index: b.category_name in /home/philobytes/www/webdesign/ccshppr/wordpress/admin_insert_new.php on line 89

Notice: Undefined index: b.category_name in /home/philobytes/www/webdesign/ccshppr/wordpress/admin_insert_new.php on line 89

Notice: Undefined index: b.category_name in /home/philobytes/www/webdesign/ccshppr/wordpress/admin_insert_new.php on line 89

Notice: Undefined index: b.category_name in /home/philobytes/www/webdesign/ccshppr/wordpress/admin_insert_new.php on line 89

Notice: Undefined index: b.category_name in /home/philobytes/www/webdesign/ccshppr/wordpress/admin_insert_new.php on line 89

Notice: Undefined index: b.category_name in /home/philobytes/www/webdesign/ccshppr/wordpress/admin_insert_new.php on line 89

Notice: Undefined index: b.category_name in /home/philobytes/www/webdesign/ccshppr/wordpress/admin_insert_new.php on line 89

Notice: Undefined index: b.category_name in /home/philobytes/www/webdesign/ccshppr/wordpress/admin_insert_new.php on line 89

Notice: Undefined index: b.category_name in /home/philobytes/www/webdesign/ccshppr/wordpress/admin_insert_new.php on line 89

Ah, my bad.

Have a look at what the $row array contains.

[php] while ($row = mysqli_fetch_array($select_category)) {
echo ‘

’; var_dump($row); die(); // add this line to debug, there are tools that do this that are awesome - but more on those later!
echo “<option value=’”.$row[‘ad_category_id’]."’>" .$row[‘b.category_name’]."";
}[/php]

ok did that now the form dies in the middle of the request so definitely something not right :slight_smile:

meh, sorry forgot to add:<label for="category">Category</label> <select name='category'><option value='0'>-Select-</option><pre>array(6) { [0]=> string(1) "1" ["category_id"]=> string(1) "1" [1]=> string(1) "2" ["category_parent_id"]=> string(1) "2" [2]=> string(7) "General" ["category_name"]=> string(7) "General" }

Well you did add a die() statement so it kinda is ^^

array(6) { [0]=> string(1) "1" ["category_id"]=> string(1) "1" [1]=> string(1) "2" ["category_parent_id"]=> string(1) "2" [2]=> string(7) "General" ["category_name"]=> string(7) "General" }

This is the row data you have available. If you want a different result set (more or less columns) then you should make the changes you want to the query.

[php]echo “<option value=’”.$row[‘category_id’]."’>" .$row[‘category_name’]."";[/php]

i may need more fields later, but the problem is I’m only seeing these options listed in the html source; I’m not seeing those choices in the actual drop down menu. the only thing I see without going to the source is “select”. and that’s been the case for each time.

The ech/var dump/die was just for temporary debug puposes to see exactly what a row contained. Remove that line again and modkfy the option line according to what you wanted to display.

Ok finally figured that out. I currently have: [php]Category

<?php require_once 'philobytes_login.php'; //servername //dbname //username //password // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } //error_reporting(E_ALL); //ini_set('display_errors', 1); $query = "SELECT category_id, category_name from wp_awpcp_categories"; $select_category = mysqli_query($conn, $query); echo ""; echo "-Select-"; while ($row = mysqli_fetch_array($select_category)) { //echo '
'; var_dump($row); die();
   echo "" . $row['category_name'] . "";
  }
  $conn->close();
echo "";
?>[/php]

Now this is ok, but now I realize I need to add the “category_parent_id” for each entry as this was a multi-level menu in the db. Changing the echo "<option value='" . $row['category_id'] ."'>" . $row['category_name'] . "</option>"; row to echo "<option value='" . $row['category_id'] . $row['category__parent_id'] ."'>" . $row['category_name'] . "</option>"; I get the parent_ids but not the names

Why are you echoing html? Get rid of that mess. See below…

  • And you dont have to manually close the DB connection. Php will do it automatically.

[php]<?php
$query = “SELECT category_id, category_name from wp_awpcp_categories”;
$select_category = mysqli_query($conn, $query);
?>

-Select- <?php while ($row = mysqli_fetch_array($select_category)) : ?> '><?= $row['category_name'] ?> <?php endwhile;?> [/php]
Sponsor our Newsletter | Privacy Policy | Terms of Service