confused- i need suggestions!

adamhhh1

ok ive got the system where you have an item- this item is called a destination (as in railway destination).

Each destination is printed out.

However i then needed to create id's in the database which provide a link to another destination- because these destinations are related.

I then need to print out the related destination on the same page. The problem is the original destination uses 6 or 7 queries to print out (had to use loads of queries as there are lots of different paramaters and database tables!).

These queries are of course looking at the unique ID for the original destination. The only way I can see to print out the related destination is to redo these queries except looking at new IDs- related IDs.

Does anyone have any other suggestions?

Piranha

I suggest that you show some code and database structure. It seems very strange that you should need 6-7 queries to get the destination, something seems to be either coded or designed wrong if you need that.

adamhhh1

well each query is based on a different id from a different table and then they all sort of link together

Piranha

I still don't see any code or database design...

NogDog

If I understand the problem correctly, what you probably want is two database tables. One would be for each destination, so we'll call it "destination", and it will include columns for all the information exclusive to each destination in it. Then you'll have a second, relational table that defines the links (or relations) between the items in the destination table, which we'll call "links". It would just have two columns, the destination_id of one destination and that of the destination being linked to. (Possibly you could have additional columns about that link, such as distance, travel time, etc., depending on what you are trying to do.)

links
===============
from_dest_id
to_dest_id

Then, if you want to query for all the destinations linked to from a destination whose ID is 123:

SELECT destination.destination_id, destination.destination_name
FROM destination 
INNER JOIN links ON destination.destination_id = links.to_dest_id
WHERE links.from_dest_id = '123'

Installer

Have you abandoned your other thread?

adamhhh1

the problem is NogDog, im doing that however I see know way of returning all the results as in effect its a new query so Id have to do all the queries again. Can anyone think of a different way?

leatherback

Adam..
A number of times you have been asked to show some of the code you are using, and the design fo your database.

We cannot just go sit and guess in the sky of how you are handling things. You need to be specific, and we will help you to get a smooth working solution

adamhhh1

sorry, theres so much code and so many tables that i didnt think it was worth it!

its more of a general problem 🙂

adamhhh1

right ive written the problem down on paper myself, here is what it is->

Get and print an item based on multiple query
See if there is a related item(s)
there is then a need to get and print this related item using the same queries as the original item to get its information from a number of different tables and fields
obviously I can redo each query based on this related item.
However the problem is the number of queries, there would just be dozens of queries if I redid each one, especially as there could be tons of related items for one item.
Is there a theory to get around this problem without creating millions of queries? I dont need code, just ideas!

leatherback

Adam: Did you check the example I gave in your other thread on this

adamhhh1

yeh ive looked at joins, however there are tons of different ids that each table is based upon.

Also it would still mean id have to do a new query for each related item.

leatherback

adamhhh1 wrote:
yeh ive looked at joins, however there are tons of different ids that each table is based upon.

Also it would still mean id have to do a new query for each related item.

I cannot get past the idea that your database design is flawed, or else you have perhaps not much of an idea how to work properly with relational databases. In either case.. We cannot really help you as long as you cannot make it clear what exactly you are doing, and why you cannot use joins.

edit: What do you mean with: "There are lots of ids that every table is based on"? Doesn't make sense as you write it...

adamhhh1

so one table gets an id from another table, then there is another table with information about same item, except looking at a different id.

leatherback

.. Sounds like joins to me. Show us some code

adamhhh1

ok you asked for it 😃

so at the bottom im starting to return the related item.

$query = "SELECT raillink_destination_id, place_id, raillink_id, ticket_name FROM raillink_destinations
WHERE place_id = $place_id";

$result = mysql_query($query)
or die ("The system doesn't like your login details- please try again
with different ones.");

$row = mysql_fetch_assoc($result);

$place_id2 = $row['place_id'];
$raillink_id = $row['raillink_id'];
$ticket_name = $row['ticket_name'];
$raillink_destination_id = $row['raillink_destination_id'];

//echo $raillink_destination_id;
//echo $place_id2;
//echo $raillink_id;

$query2 = "SELECT raillink_id, toc_id, name FROM raillinks WHERE raillink_id = $raillink_id";

$result2 = mysql_query($query2)
or die ("The system doesn't like your login details- please try again
with different ones.");

$row2 = mysql_fetch_assoc($result2);

$toc_id = $row2['toc_id'];
$name = $row2['name'];

//echo $toc_id;

$query3 = "SELECT toc_id, name FROM tocs WHERE toc_id= $toc_id";

$result3 = mysql_query($query3)
or die ("The system doesn't like your login details- please try again
with different ones.");

$row3 = mysql_fetch_assoc($result3);

$toc_id = $row3['toc_id'];
$name2 = $row3['name'];

//echo $name2;

$query4 = "SELECT place_id, place_name FROM places WHERE place_id= $place_id";

$result4 = mysql_query($query4)
or die ("The system doesn't like your login details- please try again
with different ones.");

$row4 = mysql_fetch_assoc($result4);

$place_name = $row4['place_name'];

//////////////////////////////////////////////////////////////////////////////////////
// problems here in next two queries
//////////////////////////////////////////////////////////////////////////////////////

$query5 = "SELECT nlc_code, raillink_destination_id FROM raillink_interchange_stations
WHERE raillink_destination_id = $raillink_destination_id";

$result5 = mysql_query($query5)
or die ("The system doesn't like your login details- please try again
with different ones.");

while($row5 = mysql_fetch_array($result5))
{
$nlc_code = $row5['nlc_code'];
//echo $nlc_code;
//echo "<br />";

$query6 = "SELECT nlc_code, station_name FROM stations
WHERE nlc_code = $nlc_code";

$result6 = mysql_query($query6)
or die ("The system doesn't like your login details- please try again
with different ones.");

while($row6 = mysql_fetch_array($result6))

{
$station_name = $row6['station_name'];
// echo $station_name;
//$station_name2 = $station_name;
//echo $station_name;
}
}

// see if there is a match between the first related places field in the unique_places field
// to a place_id in the places table
$query7 = "SELECT * FROM places INNER JOIN unique_places
WHERE places.place_id = unique_places.related_place_1_id";

$result7 = mysql_query($query7)
or die ("The system doesn't like your login details- please try again
with different ones.");

$row7 = mysql_fetch_assoc($result7);

$related_place_1_id = $row7['related_place_1_id'];

// echo $related_place_1_id;

$query8 = "SELECT * FROM places WHERE place_id = $related_place_1_id";

$result8 = mysql_query($query8)
or die ("The system doesn't like your login details- please try again
with different ones.");

$row8 = mysql_fetch_assoc($result8);

$place_name2 = $row8['place_name'];

/////////////////////////////
/$connect = mysqli_connect("localhost","root","ARC0nl1n3!");
$rowcount = 0;
mysqli_select_db($connect,"rio_test");
$q = mysqli_query($connect,"SELECT nlc_code, station_name FROM stations
WHERE nlc_code = raillink_interchange_stations.nlc_code");
while ($abc = mysqli_fetch_row($q)) {
$rowcount++;
for ($k=0; $k<count($abc); $k++){
echo "<br />";
//print htmlspecialchars($abc[$k]);
$nlc_codes1 = htmlspecialchars($abc[$k]);
echo $nlc_codes1;
}
} /
// echo $station_name;

/////////////////////////

echo "<div class=\"title\">

<div style=\"float: left;\">$name</div>
<div style=\"display: inline; float: right;\">$name2</div>
</div>";
echo "<br />";
echo "Destination: <strong> $place_name, </strong>";
echo "<br /><br />";
////////////////////////////////
// get the TOC stuff
echo "<div id=\"stationname\">";
echo "Interchange Station:";

$query5 = "SELECT nlc_code, raillink_destination_id FROM raillink_interchange_stations
WHERE raillink_destination_id = $raillink_destination_id";

$result5 = mysql_query($query5)
or die ("The system doesn't like your login details- please try again
with different ones.");

while($row5 = mysql_fetch_array($result5))
{
$nlc_code = $row5['nlc_code'];
//echo $nlc_code;
//echo "<br />";

$query6 = "SELECT nlc_code, station_name FROM stations
WHERE nlc_code = $nlc_code";

$result6 = mysql_query($query6)
or die ("The system doesn't like your login details- please try again
with different ones.");

while($row6 = mysql_fetch_array($result6))

{
$station_name = $row6['station_name'];

echo "<strong> $station_name,</strong>";

}
}
echo "<br /><br />";
echo "<a href=\"../../raillinks_new/index.php?place_id=$place_id\">More Details >></a>";
// echo $place_id;

//}
echo "</div>";
if (isset($related_place_1_id)) {

echo "<br />";
echo "<div class=\"title\">

<div style=\"float: left;\">$place_name2</div>
<div style=\"display: inline; float: right;\">$name2</div>
</div>";
echo "<br />";
echo "Destination: <strong> $place_name, </strong>";
echo "<br /><br />";
////////////////////////////////
// get the TOC stuff

echo "Interchange Station: <strong> $station_name, </strong>";
echo "<br /><br />";
echo "<a href=\"../../raillinks_new/index.php?place_id=$place_id\">More Details >></a>";

}

/**if($place_results->numrows() > 0 )
{
		while($row = $place->fetchrow())
	{
		$raillink_id = $row['raillink_id'];
	}
} **/

//while ($row = mysql_fetch_assoc($result))
// echo $row["area_id"];

?>

leatherback

Looks like me you can reduce the number of queries by more then half. Please edit your code, and place [php ] [/ php ] tags around them.

leatherback

Something like this should work:


$query = "
SELECT raillink_destination_id, place_id, raillink_id, ticket_name 
FROM raillink_destinations
LEFT JOIN raillinks 
  ON raillinks.raillink_id = raillink_destinations.raillink_id
LEFT JOIN tocs
  ON raillinks.toc_id = tocs.toc_id
LEFT JOIN places
  ON raillink_destinations.place_id = places.place_id
WHERE place_id = $place_id";

As I am not sure what you try to achieve, your code is not documented, and not highlighted.. Have a look at it. I think it will sort out the other problems too.

Furthermore: It is good database practice to have a different name for colums which in different tables have a different role.
So, if something is a unique identifier, use id: raillink_id, if it is a foreign key, use nr: raillink_nr. this keeps it easier to keep track of the 'status'.

adamhhh1

nope ive tried that query and similar styles to it and it wont run the query, but thats not the point though- the problem is each new related item would just generate more queries- and there could be possibly tons of related items.

leatherback

adamhhh1 wrote:
nope ive tried that query and similar styles to it and it wont run the query, but thats not the point though- the problem is each new related item would just generate more queries- and there could be possibly tons of related items.

What do you mean 'it wont run'?

You claim that that is not the point, than obviously I do not get the point. As far as I can tell, you only have 1 user-defined variable, which is used to selct something from a database, the output of which is used to select data from another table. this is done using a join, making all other comparisons obsolete