Php/Mysql Displaying multiple shared results

[bidawinner]

Could really use some help sorting this out..

Have multiple addresses that share the same Gas Station name.. in other words the Gas Station name was entered only once in Mysql.. but Multiple addresses share the Stations name .. Problem is Only One Station is being displayed instead of all Stations on the web pages

So for example if there are 1 Shell Station (brand name) at 3 different address ..only one of them gets displayed..

I think the issue is the part of the coded I turned green ..?

.......

<?php
$querystations = "SELECT * FROM `stations` WHERE `state` = '{$State['id']}' ORDER BY name ASC";
$rowstations = $Db->Rows($querystations);
$resultstations =$Db->Query($querystations);
$querycheck = mysql_query("SELECT * FROM `prices` WHERE approved = '0' and archive != '0'");
while($checkrow = mysql_fetch_array($querycheck)) {
$ids .= str_replace('ID:', '', $checkrow['station'] .',');
}
$station_array = explode(',', trim($ids));
for($i = 0; $i < $rowstations; $i++){
$station_id = trim($Db->Result($resultstations, $i, 'id'));
$station_name = $Db->Result($resultstations, $i, 'name');
$station_cityid = $Db->Result($resultstations, $i, 'city');
if(array_search($station_id, $station_array) === FALSE)
{
$querycity = "SELECT * FROM `citys` WHERE `id` = '{$station_cityid}'";
$station_city = $Db->FetchArray($querycity);
$queryaddress = "SELECT * FROM `address` WHERE `station` = '". $Db->Result($resultstations, $i, 'id') . "'";
$station_addr = $Db->FetchArray($queryaddress);
?><tr align="center">
<td><?php echo stripslashes($station_name) ?></td>
<td><?php echo "<a href=\"./{$State['state']}/{$station_city['name_short']}.html\">{$station_city['name']}</a>";?></td>
<td><?php echo "<a href=\"http://maps.google.com/?q={$State['state']}, {$station_city['name']}, {$station_addr['name']}\">{$station_addr['name']}</a>"; ?></td>




<td>

---------- Post added at 04:39 PM ---------- Previous post was at 04:36 PM ----------

Or maybe here..

$querystations = "SELECT * FROM `stations` WHERE `state` = '{$State['id']}' ORDER BY name ASC";
$rowstations = $Db->Rows($querystations);
$resultstations =$Db->Query($querystations);


maybe it needs to query not only station names but the different addreses also in that part of the code? maybe a "JOIN" statement or something ?

I know just enough to screw it it royaly

 

[bidawinner]

Am I on the right track ? as far as somehow needing to "JOIN" the address with the Station name

 

[Kate]

You could use SELECT DISTINCT to exclude dupes. But don't use SELECT *, it's better practice to only fetch the fields that you need.
Why do you use stripslashes stuff ?

 

[bidawinner]

You could use SELECT DISTINCT to exclude dupes. But don't use SELECT *, it's better practice to only fetch the fields that you need.
Why do you use stripslashes stuff ?

Morning sd,

I am actually trying to INCLUDE duplicate Station names.. For example ..Lets say Chicago has a Shell Gas Station brand.. that Station has 4 address locations..

So in the database there is :

1 Shell Station

and 4 addresses attached to that Shell name


Our website shows a list of all the Stations in Chicago ..it will only display 1 Shell Station and address instead of all 4 ! Because all 4 addresess share the same Station Name..so the Php code needs isn't written correctly to display all 4 Stations..

Appreciate your help though

looking at the PHP code above thats why I was asking about possibly a "JOIN" statement ?

I don't know .. I don't write code .. I can play around and fix most things but cant actually write code.. this was all hired out stuff(and the coder is long gone)


I am probably way off on the "JOIN" ..I just know I need to somehow tell the script to display all the addresses associated with any particular Station name ..(for a particular State & City )

---------- Post added at 08:38 AM ---------- Previous post was at 08:15 AM ----------

 

[Eric]

As far as any type of JOIN query, what does the structure of the "stations", "citys", and "address" tables look like?

 

[bidawinner]

As far as any type of JOIN query, what does the structure of the "stations", "citys", and "address" tables look like?

Morning Eric,

address

id state city station name GLatLng GCity GState GIgnore blenderp comments Phone

States id state State Name state_short State Name used in URL comment State Comment meta_keywords Keywords for META tag description Description for on State Page lat lng GLatLng abbr


Citys

id state name name_short lng lat GLatLng


Stations

id state city name GLatLng lat lng

 

[bidawinner]

$20 for someone to fix this...


I do know that Select FROM should be "address"(table)("name" field) and not station ... and THEN after it has quereied each address in the US State ..THEN it needs to Join those addresses with a Station name from the "name" field of the Station table..and display those results

 

[smghost001]

Forget JOIN, what you need is a while loop. This code should work:

<?php
$querystations = "SELECT * FROM `stations` WHERE `state` = '{$State['id']}' ORDER BY name ASC";
$rowstations = $Db->Rows($querystations);
$resultstations =$Db->Query($querystations);
$querycheck = mysql_query("SELECT * FROM `prices` WHERE approved = '0' and archive != '0'");
while($checkrow = mysql_fetch_array($querycheck)) {
$ids .= str_replace('ID:', '', $checkrow['station'] .',');
}
$station_array = explode(',', trim($ids));
for($i = 0; $i < $rowstations; $i++){
$station_id = trim($Db->Result($resultstations, $i, 'id'));
$station_name = $Db->Result($resultstations, $i, 'name');
$station_cityid = $Db->Result($resultstations, $i, 'city');
if(array_search($station_id, $station_array) === FALSE)
{
$querycity = "SELECT * FROM `citys` WHERE `id` = '{$station_cityid}'";
$station_city = $Db->FetchArray($querycity);
$queryaddress = "SELECT * FROM `address` WHERE `station` = '". $Db->Result($resultstations, $i, 'id') . "'";
?><tr align="center">
<td><?php echo stripslashes($station_name) ?></td>
<td><?php echo "<a href=\"./{$State['state']}/{$station_city['name_short']}.html\">{$station_city['name']}</a>";?></td>
<td><?php
$raddress = mysql_query($queryaddress, $Db);
while($station_addr = mysql_fetch_array($raddress,MYSQL_ASSOC)) {
echo "<a href=\"http://maps.google.com/?q={$State['state']}, {$station_city['name']}, {$station_addr['name']}\">{$station_addr['name']}</a><br />";
} ?></td>

 

[bidawinner]

"while" did-not work.....

 

[Eric]

Morning Eric,

address

id state city station name GLatLng GCity GState GIgnore blenderp comments Phone

States id state State Name state_short State Name used in URL comment State Comment meta_keywords Keywords for META tag description Description for on State Page lat lng GLatLng abbr


Citys

id state name name_short lng lat GLatLng


Stations

id state city name GLatLng lat lng

Can you run a "DESCRIBE" query on those, so I can see the types of each field?

DESCRIBE address;

for example.

 

[bidawinner]

Fixed..Closed

Thanks SMg ..

appreciate everyones help