Why would no rows be returned for a SELECT DISTINCT query?

[excelmaster]

Hello folks,

 

In trying to improve the user experience for my first WebApp I have decided to create two new tables - one a master file to contain a list of all stores, and the second a master file to contain a list of all products that are normally purchased - and I would like to use the values from these two tables as lookup values in dropdown listboxes, to which the user can also add new entries.

 

As it turns out, I'm stuck on the very first objective i.e. to lookup/pull-in the distinct values from the master tables.

 

The problem I'm having is that the query seems to return no rows at all...in spite of the fact that there a records in the table, and the exact same query (when run within the MySQL environment) returns all the rows correctly.

 

Is there something wrong with my code, or how can I debug to see whether or not the query is being executed?

 

Objective # 2, which is to allow new values to be entered into the dropdown listbox, and then inserted into the respective table is certainly waaay beyond my beginner skills, and I'll most certainly need to some help with that as well..so if I can get some code/directions in that regard it will be most appreciated.

 

Thank you.

<?php
   $sql = "SELECT DISTINCT store_name FROM store_master ORDER BY store_name ASC";

   $statement = $conn->prepare($sql);
   $statement->execute();

   $count = $statement->rowCount();                                    
   echo $count;

   // fetch ALL results pertaining to the query
   $result = $statement->fetchAll();

   echo "<select name=\"store_name\">";
   echo '<option value="">Please select...</option>';
   foreach($result as $row)
       {
          echo "<option value='" . $row['store_name'] . "'></option>";
       }
       echo "</select>";
?>

[john_c_1984]

http://forums.phpfreaks.com/topic/285929-pdo-in-a-php-class/#post_id_1467894

 

Try this if you are using pdo. Returns a nice array already counted for you..

[ginerjm]

Do you have error checking turned on?  Do you have some more code preceding this code?

 

(Basically, where is the code creating $conn?)

Edited May 15, 2014 by ginerjm

[Psycho]

If your tables are properly structured why do you need to use DISTINCT? Your store_master should only contain one record for each unique store and the products table should contain one record for each unique product. My guess is that your query is failing. You need to add proper code to debug the DB errors.

 

As for your second request, that is quite a lot to ask in a forum post. There are a multitude of things that are required to accomplish that. First off you need to determine what the UI will look like. You could have a drop-down list with an optional text field or you could implement a custom JavaScript UI element that works like a drop-down but allows custom values to be entered. I would suggest the former to get a working solution, then you can add the latter if you want.

 

Second is you need to add the back end functionality to accept the values. You'll need to detect if a new value was sent. If so, attempt to save the value, but add handling in case it is a duplicate.

[davidannis]

You need something between option and /option or nothing will show in your list except blank lines.

   foreach($result as $row)
       {
          echo "<option value='" . $row['store_name'] . "'></option>";
       }
       echo "</select>";

should be:

   foreach($result as $row)
       {
          echo "<option value='" . $row['store_name'] . "'>{$row['store_name']}</option>";
       }
       echo "</select>";

[excelmaster]

@ginerjm: I guess I do not have error-checkign turned on....and that's what I wanted to know about as well i.e. how to turn on error-checking, and what must be turn to check for errors?

 

To answer your second question, yes I do have other preceding code (a mixture of PHP and HTML), most of which is/was working just fine...including other queries....and if you're asking specifically about the code that creates $conn, then that code is in a separate file (which is being included), and that too works just fine (because other queries are using it, and they seem to be returning the correct results).

 

Anyways, if it helps, here's the code for $conn:

<?php
function dbConnect($type) {
    $dbtype        = "mysql";

    $dbhost     = "localhost";

    $dbname        = "mydb";

    $dbuser        = "myuser";

    $dbpass        = "mypass";

    // database connection

    try {

        $conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);

        return $conn;

    } catch (PDOException $e) {

        print "Error!: " . $e->getMessage() . "<br/>";

        exit;

    }

  }

?>

@Psycho: I do not really have to use "DISTINCT"...and I even tried removing it and using "SELECT * FROM store_master" but yet no joy.

 

So yeah, how do I go about debugging the DB errors?

 

>> As for your second request, that is quite a lot to ask in a forum post.

Hmmm....I guess I'll just make-do with a plain Jane dropdown listbox and manually enter the products into the table (before-hand).

[davidannis]

Put this at the top of your php

error_reporting(E_ALL | E_STRICT | E_NOTICE);
ini_set('display_errors', '1');

[davidannis]

I will come back when I have more time and try to post an example of adding a value not in the drop down.

[ginerjm]

On what are you basing your conclusion that there are no results?  The bare echo of the count?

[excelmaster]

@davidannis:

 

>> You need something between option and /option or nothing will show in your list except blank lines.

That certainly was a very good catch, but alas (inspite of changing it to the corrected statement that you provided) it still isn't doing anything to help show the values from the lookup table.

 

Thanks for providing me the code to turn on error reporting. I seem to have a slew of errors being reported, but they don't seem to be related to the current problem though....I'm still investigating (and trying to eradicate the errors).

 

>> I will come back when I have more time and try to post an example of adding a value not in the drop down.

Much thanks in advance for this offer. It will certainly help provide a much improved user-experience. With my newbie knowledge and experience I would never be able to achieve this in a million years.

 

 

@ginerjm:

 

>> On what are you basing your conclusion that there are no results?  The bare echo of the count?

It's not based just on the bare echo of the count (which of course indicates 0), but also due to the fact that there are no (looked-up) values being shown in the dropdown listbox.

[ginerjm]

So - have you added the error checking and added code to check the results of your query call?  If you re-run your code with those two things you should definitely see some error messages to help you.  At this point - you query is so simple that there must be something wrong like an invalid column name or table name.

[excelmaster]

@ginerjm:

 

>> At this point - you query is so simple that there must be something wrong like an invalid column name or table name.

That's exactly what I said to myself last night - "This query is so simple, and not only is it simple, but it's also one that is working for another table"....that I figured there was something crazy going on.

 

Guess what? The problem had nothing to do with any of the code I had in my file! I guess we were all "barking up the wrong tree", 'eh?

 

Anyways, here's how I stumbled across the problem, quite accidentally, if you ask me: Late last night I casually went in to the SQL interactive environment (signing-on as a regular user...rather than as root) to check if my table names were correct. So I issued the command "SHOW TABLES;" and lo and behold, the two lookup tables weren't even listed (even though the main table [shoplist] was indeed listed). It then dawned on me that I had inadvertently forgotten to "GRANT ALL...." to regular users for the two new lookup tables (when I had created the tables as root). Once I executed the "GRANT ALL..." command everything worked hunky-dory.

 

So, lesson for self, and note to moderators/guru's here: should a similar post crop-up in future (where a person can see results from one table, but not from others), first thing to ask is if access rights were granted for the "problem" tables to regular users.

 

@davidannis: What's the best way for you to provide me the code for "adding a value not in the drop down" - as and when you have the time to find/code it - now that I've marked this thread "Solved"?

[ginerjm]

What you should really learn from this is to ALWAYS DO ERROR CHECKING whenever you do something (as I say) 'external'.  A call to your db server (a query!) is external so you should always do some bit of code that will verify that what you 'think' happened actually Did happen.  A simple check on the query result var and display of MySQL_error would have solved this before you even made your first post here.

 

Glad you solved it.

[Jacques1]

I think the lesson should rather be: Turn your friggin' errors on!

 

http://www.php.net/manual/en/pdo.setattribute.php

 

 

[davidannis]

Here's what I'd do on question 2:

You need a select with an "Other" or not in list value. In this example I have one for corporate structure:

<p>Corporate Structure:<SELECT NAME="corp_struc_code"  id="corpstructure"   onclick='corpstructurejs()' >
<OPTION VALUE="OTH"> Other </OPTION><OPTION  SELECTED  VALUE="CCORP"> C Corporation </OPTION>
<OPTION  VALUE="LLP"> Limited Liability Partnership </OPTION><OPTION  VALUE="PARTNE"> Partnership </OPTION>
<OPTION  VALUE="SCORP"> S Corporation </OPTION><OPTION  VALUE="PROPRI"> Sole Proprietorship </OPTION>
<OPTION  VALUE="LLC"> The company is organized as an LLC </OPTION></select></p>
<div id="corpstructurejsOTH" style="display: none;"><textarea name="corporate_structure" cols="70" rows="4" >YOU NEED TO ECHO THE OTHER VALUE HERE IF IT ALREADY EXISTS</textarea></div>

When you click on the SELECT LIST it executes the corpstructurejs()

In it you need a function to show a text input if the value is not in the list:

 <script type="text/javascript"> 
                function corpstructurejs() { if (document.getElementById('corpstructure').value == 'OTH') {
document.getElementById('corpstructurejsOTH').style.display = 'block' ;
} else {
        document.getElementById('corpstructurejsOTH').style.display = 'none';
    }} </script>

Then on SUBMIT, you need to record the answer. In this case, I store the other value in a different table, so I've quickly editted a bit of code to do what you want but it is untested.

    $rfs = mysqli_real_escape_string($link, $_POST['reason_for_selling_code']);
    if ($rfs != 'OTH') {
        $query = "SELECT * from select_reason_for_selling where code='" . $rfs . "'";
        $result = mysqli_query($link, $query);
        $reason = mysqli_fetch_assoc($result);
    } else {
        $select = "INSERT INTO mytablename VALUES ('$my_escapedvalue1','$my_escaped_value_2')";
        // Do the rest of the insert into your table here
    }
//do something with their choice here. 

[excelmaster]

@ginerjm and @Jacques1: I'm new to PHP coding...learning as I go (thanks to you guys)....which is why I had no idea how (and what) to do in order to setup error checking. Thanks for the link @Jacques1...I will take a look at it for future use.

 

@davidannis: Thanks for putting together that code! No worries that it's not tested...I will test and fix if required (to the best of my newbie abilities). Much appreciated

 

Cheers guys!