Help with returning a list of data from database

MySQL database
#1

I’ve been stumped by this on! I know the solution is probably an easy one, (I hope!) but I have not been able to get it to work right.

I’m using the following code to create a drop down for the site visitor to select a state that they would like to view the info for:
[php]<?php
require(“dealer_map_dbinfo.php”);
// Write out our query.

mysql_connect($local_host,$username,$password);
@mysql_select_db($database) or die( “Unable to select database”);

$state_name_query = “SELECT state_name FROM states_list”;

// Execute it, or return the error message if there’s a problem.

echo “”;

$state_name = mysql_query($state_name_query) or die(mysql_error());

$dropdown = “”;

while($row = mysql_fetch_assoc($state_name)) {
	$dropdown .= "\r\n<option value='{$row['state_name']}'>{$row['state_name']}</option>";
}

$dropdown .= “\r\n”;

echo $dropdown;
mysql_close();
?>

[/php]

This is working, as far as I can tell. It’s when you click the view list and are taken to the “results” page that I am running into the problem.
this is the code of the “results” page:
[php]<?php
require(“dealer_map_dbinfo.php”);

mysql_connect($local_host,$username,$password);
@mysql_select_db($database) or die( “Unable to select database”);

$state_input=$_POST[‘state_input’];

$state_query=mysql_query(“SELECT state_abv FROM states_list WHERE state_name=’$state_input’”);
$state_array=mysql_fetch_array($state_query);
$state=$state_array[0];

$dealer_num_query=mysql_query(“SELECT COUNT(DISTINCT name) FROM markers WHERE state=’$state’”);
$dealer_num_array=mysql_fetch_row($dealer_num_query);
$dealer_num=$dealer_num_array[0];

echo "SICASS racing Dealers in " . $state_input . “

”;

$i=0;
while ($i < $dealer_num) {
$dealer_query=mysql_query(“SELECT DISTINCT name, address, address2, city, state, zipcode, phone, email FROM markers WHERE state=’$state’”);
$dealer=mysql_fetch_row($dealer_query);

$name=$dealer[0];
$address=$dealer[1];
$address2=$dealer[2];
$city=$dealer[3];
$state=$dealer[4];
$zipcode=$dealer[5];
$phone=$dealer[6];
$email=$dealer[7];

echo $name;
echo “
”;
echo $address;
echo “
”;
echo $address2;
echo “
”;
echo $city;
echo ", ";
echo $state;
echo " ";
echo $zipcode;
echo “
”;
echo $phone;
echo “
”;
echo $email;
echo “

”;

$i++;
}
mysql_close();
?>[/php]

I keep getting the same data repeated over and over, not multiple rows of data.
for example, On the first page I select “Michigan”.
I know that there 35 different entries in the data base that should show up, but all that appears is the first entry 35 times? ???

Any help would be greatly appreciated.

#2

Try it this way

[php]
$dealer_query=mysql_query(“SELECT DISTINCT name, address, address2, city, state, zipcode, phone, email FROM markers WHERE state=’$state’”);
$dealer=mysql_query($dealer_query);

while ($row = mysql_fetch_array($dealer) {

$name=$row['name'];
$address=$row['address'];
$address2=$row['address2'];
    .
    .
    .

echo $name;
echo “
”;
echo $address;
echo “
”;
echo $address2;
echo “
”;
echo $city;
echo ", ";
echo $state;
echo " ";
echo $zipcode;
echo “
”;
echo $phone;
echo “
”;
echo $email;
echo “

”;

}[/php]

#3

Ok so I switched the code to this:
[php]<?php
require(“dealer_map_dbinfo.php”);

mysql_connect($local_host,$username,$password);
@mysql_select_db($database) or die( “Unable to select database”);

$state_input=$_POST[‘state_input’];

$state_query=mysql_query(“SELECT state_abv FROM states_list WHERE state_name=’$state_input’”);
$state_array=mysql_fetch_array($state_query);
$state=$state_array[0];

$dealer_num_query=mysql_query(“SELECT COUNT(DISTINCT name) FROM markers WHERE state=’$state’”);
$dealer_num_array=mysql_fetch_row($dealer_num_query);
$dealer_num=$dealer_num_array[0];

echo "SICASS racing Dealers in " . $state_input . “

”;

$i=0;
while ($i < $dealer_num) {

$dealer_query=mysql_query(“SELECT DISTINCT name, address, address2, city, state, zipcode, phone, email FROM markers WHERE state=’$state’”);
$dealer=mysql_query($dealer_query);

while ($row = mysql_fetch_array($dealer) {

$name=$row['name'];
$address=$row['address'];
$address2=$row['address2'];
$city=$row['city'];
$state=$row['state'];
$zipcode=$row['zipcode'];
$phone=$row['phone'];
$email=$row['email'];

echo $name;
echo “
”;
echo $address;
echo “
”;
echo $address2;
echo “
”;
echo $city;
echo ", ";
echo $state;
echo " ";
echo $zipcode;
echo "
Phone #: ";
echo $phone;
echo "
Email: ";
echo $email;
echo “

”;

}

$i++;
}
mysql_close();
?>[/php]

and I get:
Parse error: syntax error, unexpected ‘{’ in C:\xampp\htdocs\dealer_map\list_by_state.php on line 28

Line 28 is
[php]while ($row = mysql_fetch_array($dealer) {[/php]

I thought maybe the problem was now possibly a missing ) at the end of that line, but when I add that I get:

Warning: mysql_query() expects parameter 1 to be string, resource given in C:\xampp\htdocs\dealer_map\list_by_state.php on line 26

Warning: mysql_fetch_array() expects parameter 1 to be resource, null given in C:\xampp\htdocs\dealer_map\list_by_state.php on line 28

35 times…

#4

ok got it!!!

the prob (after I added in the missing" )") was here:
[php]$dealer_query=mysql_query(“SELECT DISTINCT name, address, address2, city, state, zipcode, phone, email FROM markers WHERE state=’$state’”);
$dealer=mysql_query($dealer_query);[/php]

actually needed to be:
[php]$dealer_query=(“SELECT DISTINCT name, address, address2, city, state, zipcode, phone, email FROM markers WHERE state=’$state’”);
$dealer=mysql_query($dealer_query);[/php]

Thanks for the help! ;D

#5

You know you do not have to run the query twice you should be able to plug the $dealer_query variable into the while loop. This is a mistake that I made when I rearranged the code for you.

I usually set the query to a variable then run the query on the variable. Not just run the query directly within the operator.

#6

Mikestaps,

Please give the following a try:[php]<?php
require(“dealer_map_dbinfo.php”);

mysql_connect($local_host,$username,$password);
@mysql_select_db($database) or die( “Unable to select database”);

$state_input=$_POST[‘state_input’];

$dealer_query = sprintf(‘SELECT DISTINCT name, address, address2, city, state, zipcode, phone, email
FROM markers
JOIN states_list
ON state_name LIKE “%s”
AND state LIKE state_abv’,
mysql_real_escape_string($state_input));

echo "SICASS racing Dealers in " . $state_input . “

”;

$result = mysql_query($dealer_query) or die(mysql_error());

while ($row = mysql_fetch_assoc($result)) {
echo $row[‘name’],’
’,
$row[‘address’],’
’,
$row[‘address2’],’
’,
$row[‘city’],’, ‘,
$row[‘state’],’ ‘,
$row[‘zipcode’],’
’,
‘Phone: ‘,$row[‘phone’],’
’,
‘Email: ‘,$row[‘email’],’

’;
}
?>[/php]

*Edited to change the formatting for the output.

#7

I’ve got another question concerning this same code…

$address2 is not always populated by the array, but it is NOT NULL.

I tried:
[php]if (isset($address2)){ code}
and
if ($address2>0) {code} [/php]
but neither work.
is there a way to run a check like that?

Thx

#8

try if (empty($address2)) {code}

#9

Andrew-
if (empty()) works!
I had to use:
[php]if (empty($row[‘address2’])){
echo ‘’;}
else {
echo $row[‘address2’].’
’;}[/php]
but I get the desired result.
Thanks again!

malasho-
your code suggestion worked perfectly as well. I skipped the implode though, for address formatting reasons.
I may still take a look at doing it that after I get everything looking and working as needed, just as a learning expierence.

Thank you very much to everybody! :smiley:

Sponsor our Newsletter | Privacy Policy | Terms of Service