MYsql and DISTINCT usage

Question

Hey i have a piece of code that works great. But when i use DISTINCT as per

<?php
$category = $_GET["state"];
if (isset($_GET["page"])) { $page  = $_GET["page"]; } else { $page=1; }; 

$start_from = ($page-1) * $results_per_page;
$sql = "SELECT DISTINCT CITY 
    FROM $datatable 
    WHERE STATE='$category'
    ORDER BY CITY ASC LIMIT $start_from, $results_per_page" ;
$rs_result = $conn->query($sql); 
?> 
<table border="1" cellpadding="4" width="100%">
<tr>

<?php 
 while($row = $rs_result->fetch_assoc()) {
?> 
            <tr>

            <td> <a href="browsepage2.php?state=<? echo $row["STATE"]; ?>&city=<? echo $row["CITY"]; ?>" ><? echo $row["CITY"]; ?></a></td>

            </tr>
<?php 
}; 
?> 
</table>

The next page the rows wont show? i'm unsure of the problem. the next page's code is as follows.

<?php
$category = $_GET["state"];
$city= $_GET["city"];
if (isset($_GET["page"])) { $page  = $_GET["page"]; } else { $page=1; }; 

$start_from = ($page-1) * $results_per_page;
$sql = "SELECT *
    FROM $datatable 
    WHERE STATE='$category'
    AND CITY='$city'
    ORDER BY PROVNAME ASC LIMIT $start_from, $results_per_page" ;
$rs_result = $conn->query($sql); 
?> 
<table border="1" cellpadding="4" width="100%">
<tr>

<?php 
 while($row = $rs_result->fetch_assoc()) {
?> 
            <tr>

            <td> <a href="browsepage2.php?state=<? echo $row["STATE"]; ?>&city=<? echo $row["CITY"]; ?>&shop=<? echo $row["PROVNAME"]; ?>" ><? echo $row["PROVNAME"]; ?></a></td>

            </tr>
<?php 
}; 
?> 
</table>

I'm kind of unsure where the problem is but i can make it work if i don't use distinct.

i'd suggest you do a bit of debugging yourself first. 1st step would be to echo out the actual query and try running it directly in mysql to see if it gets results. if it doesn't then you know you have a sql problem. if it works in the database but not in your application, you have a php problem. — But those new buttons though.., Mar 24 '17 at 03:30
Just adding `DISTINCT` to your query should not break your code, only change the data possibly. — Tim Biegeleisen, Mar 24 '17 at 03:30

score 1 · Accepted Answer · edited May 23 '17 at 12:17

$sql = "SELECT DISTINCT CITY 
    FROM ...

Here you select one column, CITY.

But your link-formatting code depends on the query result including a column STATE.

<a href="browsepage2.php?state=<? echo $row["STATE"]; ?>&city=<? echo $row["CITY"]; ?>" >

Without it, the link you create will look like:

<a href="browsepage2.php?state=&city=Schenectady">

And then the next page will try to get the request parameter 'state' and find it is blank. There's no default state if the request parameter is blank.

$category = $_GET["state"];

This makes the next page's SQL query:

$sql = "SELECT DISTINCT CITY 
    FROM $datatable 
    WHERE STATE=''
    ORDER BY CITY ASC LIMIT $start_from, $results_per_page" ;

I would guess you have no records in your database that match state=''.

P.S.: By the way, you should really start using prepared queries with parameter placeholders, to prevent your web page from being hacked. Read How can I prevent SQL injection in PHP?

ah i see. is there a better way to go about fixing it rather than having 2 seperate mysql queries? — Charles Park, Mar 24 '17 at 04:03
that was a silly question. i could just select multiple rows — Charles Park, Mar 24 '17 at 04:17

MYsql and DISTINCT usage

1 Answers1