returning multiple results by procedural?

[adamhhh11]

without using object oriented code is there a way to return multiple results from a database- as everything ive tried doesnt work??

my code at the moment-

//

$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'];

// etc

[MarkR]

You can't read multiple results with the mysql_ functions, as they don't support that feature in the client library.

You can do it with mysqli_ functions or PDO, but ONLY if they're built against the right client library version.

Object oriented code is irrelevant.

Mark

[adamhhh1]

hi ive tried lots of different examples that i found using mysqli but still not much luck.

This one below outputs the result but as one long string and i cant figure out how to return it as a usable variable.

$cv = mysqli_connect("localhost","root","pass");
$rowcount = 0;
mysqli_select_db($cv,"rio_test");
$q = mysqli_query($cv,"select nlc_code from raillink_interchange_stations WHERE raillink_destination_id = 164");
while ($abc = mysqli_fetch_row($q)) {
$rowcount++;
print "<tr>";
for ($k=0; $k<count($abc); $k++){
print "<td>".htmlspecialchars($abc[$k])."</td>";
}
print "</tr>";
}

[Installer]

The query in your second post retrieves only one field from each row, so it should be just one string, with no need to loop through each row:

while ($abc = mysqli_fetch_row($q)) {
$rowcount++;
print "<tr>";
print "<td>".htmlspecialchars($abc[0])."</td>";
print "</tr>";
}

If that's more or less what you wanted to do in the code in your first post, you could do it like this:

while ($row = mysql_fetch_assoc($result) {
    echo '<tr>';
    echo '<td>' . $row['raillink_destination_id'] . '</td>';
    echo '<td>' . $row['place_id'] . '</td>';
    echo '<td>' . $row['raillink_id'] . '</td>';
    echo '<td>' . $row['ticket_name'] . '</td>';
    echo '</tr>';
}

[MarkR]

I think what you're trying to do is return MULTIPLE ROWS, not MULTIPLE RESULTS.

Multiple results is something entirely different.

The above query will only return one result, even though it may contain more than one row.

Any MySQL API can return multiple rows, easily. See the documentation for more details.

Mark

[adamhhh1]

oops yeh i need multiple rows!

[adamhhh1]

any more ideas? i still cant figure this out 🙁

[Installer]

What have you tried? Are you getting error messages? Are you getting any results from your query? What query have you settled on? In what way does my previous post not work for you?

[adamhhh1]

basically installer i have this query now-

query5 returns multiple nlc_codes when asked. Im then using these nlc_codes as a variable to try and return multiple stations in query6, however it is only giving me one station. Any suggestions?

$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;
//echo $station_name;
}

[adamhhh1]

i think it might be fixed now- i tried nesting the second query inside the 1st one.

Is there a way to echo all the results outside this query though?

[adamhhh1]

adamhhh1 wrote:

i think it might be fixed now- i tried nesting the second query inside the 1st one.

Is there a way to echo all the results outside this query though?

-- fixed too 🙂

[leatherback]

Adam: You might want to have a look at JOINS, as it looks like you are just querying one table at the time, and retrieve the data from the next table based on results of the first, which is really a waste of your programming time 🙂

example:

$query5 = "
SELECT stations.nlc_code, raillink_destination_id, station_name 
FROM raillink_interchange_stations
LEFT JOIN stations 
  ON raillink_interchange_stations.nlc_code = stations.nlc_code 
WHERE raillink_destination_id = $raillink_destination_id";

[adamhhh1]

its nearly fixed but im not sure how to pass multiple results into a query and get multiple results from this new query.

So for example-

$query7 = "SELECT * FROM unique_places WHERE place_id = $place_id";

$result7 = mysql_query($query7)
or die ("This entry hasn't been found.");

while($row7 = mysql_fetch_array($result7))
{
$related_place1 = $row7['related_place_1_id'];

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

$result8 = mysql_query($query8)
or die ("This entry hasn't been found.");

while($row8 = mysql_fetch_array($result8))
{
	$new_place_id = $row8['place_id'];
	echo $new_place_id;
}
}

gives me two different values into $new_place_id which is fine.

then however these 2 values need to go into another query below and give me multiple results:

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

$result = mysql_query($query)
or die ("This entry hasn't been found.");

$row = mysql_fetch_assoc($result);

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

However this query refuses to run, any ideas?

[adamhhh1]

ok it looks like it works now, as one big nested while loop, phew 🙂