Query block works fine in mysql but in php doesn't

Beginners - Learning PHP
#1

I have a database called usCities with fields loc_Id, city_Name, state_Ab, state_Id all as text fields. I have a query that creates a temp array of only cities in a specific state based on state_Id. I then query this temp array to find the specific city line I am looking for to return the loc_Id I have assigned to that city. When I run the queries in mysql from phpmyadmin they work but when I convert it into php it doesn’t. Here’s my php code: where $state and $city come from a form option dropdown box
[php]

$line2=mysql_query(“SELECT DISTINCT * FROM usCIties WHERE state_Id =$state”);
$result4=mysql_fetch_array($line2);
$line3=mysql_query(“SELECT * FROM $result4 WHERE city_Name = $city”);
$result4 = mysql_fetch_array($line3);
$LocId=$result4([‘loc_Id’];

[/php]

I have tried this line but the combo returns nothing but blank page. The above at least returns my other page $result info from working code.

[php]

$line2=mysql_query(“SELECT DISTINCT * FROM usCIties WHERE state_Id =$state AND city_Name = $city”);

[/php]

#2

The last $result4 is going to cancel out the first one. Change it to result5 or something else. Please post your database schema and a clear explanation of what data you are trying to get. I suspect you have a bad database design based on the code you presented.

#3

my mistake I typed the code in wrong it is $result5 on the two bottom lines. Not sure how much clearer I can be about what I want returned from the query. I need my loc_Id, which is a DISTINCT number to each and every city in the US, from the database. Since the schema wasn’t clear before I’ll write it a different way.

Field 1 loc_Id (text) (U)
Field 2 city_Name (text)
Field 3 state_Ab (text)
Field 4 state_Id (text)

The user has already clicked on two dropdown boxes and hit submit. The data passed to the php is correct and verified. The php module takes two values from the boxes and uses them as $state and $city. From here I need the queries to return the loc_Id value of the city for processing. Since there are many states with same named cities in them the need for the double query is necessary.

#4

Really hard to go about it this way. If you dont mind, email me a zip of your files and a dump of the DB. It wont take me but a minute to do it. Just post the results to the board for others the benefit.

#5

Ok files sent

#6

In a nutshell I modified a query in his main file to get the data he was not getting. The code is poorly written and would cause way more confusion if it was posted. Additionally it had too many query’s that were not needed.

#7

Since I had some time this evening I re-wrote the code for you properly and 100% XHTML Complient.

[php]

h1 {color: navy} <? date_default_timezone_set('America/Los_Angeles'); mysql_connect("localhost", "root", ""); mysql_select_db("eric");

if (isset($_POST[‘submit’]))
{
$ip = $_SERVER[‘REMOTE_ADDR’];
$long = ip2long($_SERVER[‘REMOTE_ADDR’]);
$date = date(‘Y/m/d h:i:s a’, time());

$sql    = "SELECT * FROM usCities WHERE loc_Id={$_POST['subcat']}";
$result = mysql_query("$sql");
$row    = mysql_fetch_array($result);

echo <<<EOT

Value of loc_Id = {$row['loc_Id']}
Value of city_Name = {$row['city_Name']}
Value of state_Ab = {$row['state_Ab']}
Value of state_Id = {$row['state_Id']}
Value of \$long = $long
Value of \$ip = $ip
Value of date_Entry = $date

EOT; exit; } else { ?>

Please Select your State and City

State Select one <?php $query = mysql_query("SELECT state_Ab,state_Id FROM usStates"); while ($result = mysql_fetch_array($query)) { if (isset($_GET['mystate']) && ($_GET['mystate'] == $result['state_Id'])) { echo "$result[state_Ab]\n"; } else { echo "$result[state_Ab]\n"; } } // End While echo "\n

\n"; if (isset($_GET['mystate'])) { $query2 = mysql_query("SELECT DISTINCT city_Name, loc_Id FROM usCities WHERE state_Id={$_GET['mystate']}"); echo "

City Select one\n"; while ($result2 = mysql_fetch_array($query2)) { echo "$result2[city_Name]\n"; } echo "\n

\n"; } ?>

<?php } ?>

[/php]

#8

benanamen, Appreciate the work you did trying to help. I see the code you wrote above works well but not for my needs. I had only sent you the 10% of the page coding that was not working. The revamp of the entire code conflicts with other coding on the page.

Thanks to Marcus27 for finding my minor error. I was referencing the wrong variable in the option value, I needed to add loc_Id to the query and reference it instead of city_Name. My code needed to be changed to:

$query2 = mysql_query(“SELECT DISTINCT city_Name, loc_Id FROM usCities WHERE state_Id =$state”);
…
echo “$result2[‘city_Name’]”;

Sponsor our Newsletter | Privacy Policy | Terms of Service