[SOLVED] SQL - Help!

[monkeybidz]

Whats wrong with this picture?

 

$sql = "SELECT a.city AS $city_details1, a.state_prefix AS $state1, b.city AS $city_details2, b.state_prefix AS $state2 FROM zip_code WHERE a.zip_code='$zip1' AND b.zip_code='$zip2'";
$city_details1 = mysql_query($sql['a.city']);
$city_details2 = mysql_query($sql['b.city']);
$state = mysql_query($sql['a.state_prefix']);
$state = mysql_query($sql['b.state_prefix']);

 

$zip1 & $zip2 are already echoed on page and have a value. They are results from another page with a form.

[hemlata]

Hello,

 

Have you run your query on mysql? Is it returning results?

 

Regards,

[shocker-z]

yoiur treating $sql as an array but it isn;t try this

 

 

<?php
$query = mysql_query"SELECT a.city AS $city_details1, a.state_prefix AS $state1, b.city AS $city_details2, b.state_prefix AS $state2 FROM zip_code WHERE a.zip_code='$zip1' AND b.zip_code='$zip2'";
$sql=mysql_fetch_array($query);

$city_details1 = $sql['a.city'];
$city_details2 = $sql['b.city'];
$state = $sql['a.state_prefix'];
$state = $sql['b.state_prefix'];
?>

 

if you have multiple records returned from your query then you would need

 

while ($sql=mysql_fetch_array($query)) {

 

but would have to change other things also

 

Regards

Liam

[Barand]

remove $ from column aliases (SQL, not PHP)

 

You can't use tablenames or table aliases when getting the column from the result. You can only use column names or column aliases.

 

eg

$city_details1 = $sql['city_details1'];

[shocker-z]

cheers for that barand didnt see that

 

monkey try this

<?php
$query = mysql_query"SELECT a.city AS `city_details1`, a.state_prefix AS `state1`, b.city AS `city_details2`, a.state_prefix AS `state1`, b.state_prefix AS `state2` FROM zip_code WHERE a.zip_code='$zip1' AND b.zip_code='$zip2'";
$sql=mysql_fetch_array($query);

$city_details1 = $sql['city_details1'];
$city_details2 = $sql['city_details2'];
$state = $sql['state1'];
$state = $sql['state2'];
?>

 

 

 

 

Liam

[monkeybidz]

You put a.state twice, do i change one of those to b.state?

[monkeybidz]

Get this:Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

[shocker-z]

Try this

 

<?php
$query = mysql_query("SELECT a.city AS `city_details1`, a.state_prefix AS `state1`, b.city AS `city_details2`, b.state_prefix AS `state2` FROM zip_code WHERE a.zip_code='$zip1' AND b.zip_code='$zip2'") or die('Error in query: '.mysql_error());
$sql=mysql_fetch_array($query);

$city_details1 = $sql['city_details1'];
$city_details2 = $sql['city_details2'];
$state = $sql['state1'];
$state = $sql['state2'];
?>

 

 

Liam

[BlueSkyIS]

yep: always follow mysql_query() with or die(mysql_error());

 

 

[monkeybidz]

i can pretty much get a result if i just use this:

 

$sql = 'SELECT * FROM `zip_code` WHERE city=city AND state_prefix=state_prefix AND zip_code='$zip1' ';

$city_details1 = $sql['city'];

 

But what i am trying to do is get more than one result. One for $zip1 and another for $zip2.

This is why i used a and b in a.city=city_details1 and b.city.

 

HELPPPPPP!!!!!

[Barand]

Tell us the structure of your table/s and what you are try to do

[monkeybidz]

Table Name = zip_code

 

Table Feilds:

zip_code      //This field is OK zip_code='$zip1' or zip_code='$zip2'

city

state_prefix

 

When i define the zip_code as zip_code='$zip1' ... zip1 is echoed from the current page and has a value of whatever zip code the user has entered in the form. Same goes for zip2.

 

What i am trying to do is get city and state_prefix data for a defined zip_code from database.

 

In other words, when a user submits a form and needs info for zip code 90255 the zip code is echoed on the results page thus giving me $zip1=90255. The query is then to retrieve data for this zip code such as city and state abrreviated.

[monkeybidz]

This is the original code, but all i need out of this is zip_code, city and state_prefix

 

$sql = "SELECT lat AS lattitude, lon AS longitude, city, county, state_prefix, 
              state_name, area_code, time_zone
              FROM zip_code 
              WHERE zip_code='$zip'";
              
      $r = mysql_query($sql);
      if (!$r) {
         $this->last_error = mysql_error();
         return false;
      } else {
         $row = mysql_fetch_array($r, MYSQL_ASSOC);
         mysql_free_result($r);
         return $row;       
      }

[Barand]

$zip1=90255;

 

$sql = "SELECT city, state_prefix FROM zip_code

        WHERE zip_code = '$zip1' ";

[monkeybidz]

So how do i display city and state_prefix?

 

city_details1 = $sql['city'];

state1 = $sql['state_prefix'];

 

Is this correct?

 

[Barand]

<?php
$zip1=90255;

$sql = "SELECT city, state_prefix FROM zip_code
         WHERE zip_code = '$zip1' ";
         
$res = mysql_query($sql);

$row = mysql_fetch_assoc($res);

echo "City : " . $row['city'] . '<br/>';
echo "State : " . $row['state_prefix'] . '<br/>';



?>

[monkeybidz]

Now thats what i was looking for. I got it to work by doing this:

$sql = "SELECT city, state_prefix FROM `zip_code` WHERE zip_code='$zip1' ";
$sql2 = "SELECT city, state_prefix FROM `zip_code` WHERE zip_code='$zip2' ";

$res = mysql_query($sql);
$res2 = mysql_query($sql2);

$row = mysql_fetch_assoc($res);
$row2 = mysql_fetch_assoc($res2);

$city_details1 = $row['city'];
$city_details2 = $row2['city'];
$state1 = $row['state_prefix'];
$state2 = $row2['state_prefix'];

 

This gives me a result city and state_prefix for both  zip1 and zip2. Im sure there is an easier way, but right now i'll take what i got. LOL!

Thank a bunch!

[Barand]

<?php
$zip1=90255;
$zip2=90256;

$sql = "SELECT city, state_prefix FROM zip_code
         WHERE zip_code IN ('$zip1', '$zip2') ";
         
$res = mysql_query($sql);

while ($row = mysql_fetch_assoc($res))
{
    echo "City : " . $row['city'] . '<br/>';
    echo "State : " . $row['state_prefix'] . '<br/>';
}
?>