Having Problem Populating a Select/drop down menu Using Data from a MySQL table

[scoot]

Hi All,

 

I'm having a problem populating a Select/drop down menu using data from a MySQL table.  Below is the code.  It will build the select drop down but nothing is in it (won't drop down).  In the code below, you'll see that I put in a test to ensure the query is actually pulling back rows.  It's finding the two rows in the table, which is expected.  The name of the fields in the DB are 'compid' and 'comp_name'.

 

To see the issue, click the below link and scroll down to where it says "Please select the name of the company...".

 

http://www.isomgalaxy.com/bookproj/rackspot/regslot_1.php

 

Anybody have any ideas?  Thanks in advance.

 

Scott

 

 

    <p><?php

    

    // Connect to the DB

    require ('../../../mysqli_connect_rackspot.php'); 

    

    // Set up query to build company menu drop down

    $q2 = "select compid, comp_name from company";

    

    $r2 = @mysqli_query($dbc,$q2);

    

    $num2 = mysqli_num_rows($r2);

    

    echo '<p>Number of rows found in company query ='. $num2 . '</p>';

            

    // echo $r2[0];

    

    // build the company select menu using PHP

    echo '<p>Please select the name of the company:  ';

    echo '<select name="company">';

    while ($row2 = mysql_fetch_array($r2, MYSQLI_ASSOC)) {

        echo '<option value="'. $row2['compid'] . '">' . $row2['comp_name'] . '</option>';

    }

    echo '</select></p>';

    

    mysqli_close($dbc); // clost the DB connection

    

    ?>

[benanamen]

Your query result is $r2 but you are calling $row2

 

​And get rid of the @. Error messages are your friend.

[scoot]

Are you referring to the " while ($row2 = mysql_fetch_array($r2, MYSQLI_ASSOC)) "?

 

The below code works for me when building a table from a DB.  Based on this, shouldn't the $row2 reference be working?

 

    while($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {

        echo '<tr><td align="left">' . $row['name'] . 

                '</td><td aling="left">' . $row['dr'] . '<td></tr>';

    }

 

 

I never saw an error message for using '@'.  I took it out.  Didn't seem to help.

[benanamen]

I miss posted. Long day.

 

After $r2 = @mysqli_query($dbc,$q2); put this and tell me what you get.

 

echo "<pre>";

print_r($r2);

echo "</pre>";

die;

Edited October 23, 2015 by benanamen

[scoot]

Hi Ben,

 

Thanks for your help.

 

I added that code in the suggested location.  There is no output.  Also, the select box now does not show up. 

 

Below is the link but since php is involved it won't show much.

 

http://www.isomgalaxy.com/bookproj/rackspot/regslot_1.php

 

My mind is blown.  I'm picking this back up tomorrow.  Thanks again.

 

Scott

[scoot]

Hello AGain, Ben.

 

I tweaked it further.  I think I'm getting what you wanted to see, which is this:

mysqli_result Object(    [current_field] => 0    [field_count] => 2    [lengths] =>     [num_rows] => 2    [type] => 0)

See full page:

 

http://www.isomgalaxy.com/bookproj/rackspot/regslot_1.php

[benanamen]

Wrong data, my bad.

 

Do this, 

 

   $q2 = "select compid, comp_name from company";

   $r2 = mysqli_query($dbc,$q2);

   $row2 = mysql_fetch_array($r2, MYSQLI_ASSOC);

 

echo "<pre>";

print_r($row2);

echo "</pre>";

die;

Edited October 23, 2015 by benanamen

[scoot]

Hi Ben, 

 

That latest code doesn't seem to be giving us anything. You can click the link below to see what the web page is showing.  The current php code with your suggestion is below that.

 

http://www.isomgalaxy.com/bookproj/rackspot/regslot_1.php

 

Again, thanks for your help.

 

Scott

 

<?php

 

    

 

    // Connect to the DB

 

    require ('../../../mysqli_connect_rackspot.php'); 

 

    

 

    // Set up query to build company menu drop down

 

    $q2 = "select compid, comp_name from company";

 

    

 

    $r2 = mysqli_query($dbc,$q2);

 

$row2 = mysql_fetch_array($r2, MYSQLI_ASSOC);

 

echo "<pre>";

print_r($row2);

echo "</pre>";

die;

 

    //echo "<pre>";

    //print_r($r2);

    //echo "</pre>";

    //die;

 

    $num2 = mysqli_num_rows($r2);

 

    

 

    echo '<p>Number of rows found in company query ='. $num2 . '</p>';

 

            

 

    // echo $r2[0];

 

    

 

    // build the company select menu using PHP

 

    echo '<p>Please select the name of the company:  ';

 

    echo '<select name="company">';

 

    while ($row2 = mysql_fetch_array($r2, MYSQLI_ASSOC)) {

 

        echo '<option value="'. $row2['compid'] . '">' . $row2['comp_name'] . '</option>';

 

    }

 

    echo '</select></p>';

 

    

 

    mysqli_close($dbc); // clost the DB connection

 

    

 

    ?>

[benanamen]

Lets make this easy, give me a sql dump of your DB and your current php code.

 

 

Meanwhile, fill in the db connection info and put this page up for me.

<?php

$hostdb   = '';
$dbname   = '';
$username = '';
$password = '';
$table    = 'company';

    $pdo = new PDO("mysql:host=localhost;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql  = "SELECT * FROM $table";
    $stmt = $pdo->prepare($sql);
    $stmt->execute();

    $result = $stmt->fetchAll();

            echo '<pre>';
            print_r($result);
            echo '</pre>'; 
?>

Edited October 23, 2015 by benanamen

[scoot]

Hi Ben,

 

Your requested page is up:

 

http://www.isomgalaxy.com/bookproj/rackspot/for_benanamen.php

 

I've also attached the page in question and the DB dump.  I had to attach the DB dump as a PHP array because the site wouldn't allow .sql or .csv files to be attached.

 

Thank you greatly for the assistance.

 

Scott

regslot_1.php

rackspot.php

[benanamen]

All you have to do is change extension on the sql dump to something like .txt

[scoot]

Here is that dump in .sql format but with the extension changed to .txt.

rackspot.txt

[benanamen]

Ok, looka here young man, see what you did?

 

$row2 = mysql_fetch_array($r2, MYSQLI_ASSOC);    

 

You do remember you are using Mysqli right?

 

 

I think we should get you on PDO, it's just better. Not an opinion, it just is.

Edited October 23, 2015 by benanamen

[benanamen]

Okay... movin on.

<?php
$hostdb   = 'localhost';
$dbname   = 'phphelp_rackspot';
$username = 'root';
$password = '';
$table    = 'company';

    $pdo = new PDO("mysql:host=localhost;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql  = "SELECT * FROM $table";
    $stmt = $pdo->prepare($sql);
    $stmt->execute();

    $result = $stmt->fetchAll();
?>
<form action="http://" method="post">
<select name="company" size="1">
<?php foreach ($result AS $row) : ?>
<option value="<?= $row['compid'] ?>"><?= $row['comp_name'] ?></option>
<?php endforeach; ?>
</select>
</form>

Edited October 23, 2015 by benanamen

[scoot]

All that hair pulling, and it all came down to forgetting the 'i' in mysqli_fetch_array.

 

It's working now, of course.

 

Thank you greatly, Benanamen.

 

Regarding your suggestion to use PDO, where do you learn about that?  I'm obviously a newb and I've been studying this book titled 'PHP and MySQL for Dynamic Web Sites' by Larry Ulman.  Oddly, I don't see that he has an example to populate a drop down.  Besides that, I've had mixed feeling about the book from the content itself to the presentation of the ebook.

 

Are you aware of a book or source for a somewhat-beginner to get up to speed on PHP and mysql, like one that actually talks about PDO instead of mysqli?

[benanamen]

There are PDO tutorials all over. Just google.

 

One of the things I want to point out, you want to have good error checking in place so you know exactly what goes wrong and where. Had you had that in place this would have been handled much easier and faster.

 

When you start getting down on PDO I will show you how to set up your error catching if you haven't learned it. It should be the base of any project you start and will keep you moving along in your development.