2 prepared mysqli queries

[Loc]

Hi, I'm pretty new to mysqli and I have no idea on how to combine 2 queries.

$sql = "SELECT Name,ID FROM Categories WHERE Level=0 ORDER BY Name";

if($stmt = $mysqli->prepare($sql)) {
	if(!$stmt->execute()) {
		echo 'The query could not be executed: '.$stmt->error;
	} else {
		$stmt->bind_result($Name,$ID);

	while($stmt->fetch()) {
		echo "<li>" . $Name;

		$secondSql = "SELECT Name,Link,abbr FROM Categories WHERE belongsTo='$ID' ORDER BY Name";
		// I can't make a new query here

		echo "</li>";
	}
}

$stmt->close();
} else {
	 echo 'Incorrect Query: '.$mysqli->error;
}

Where you see the comment I cannot make a second query, If I put a second prepared query there it says "Commands out of sync; you can't run this command now" and if I put a normal query there ($mysqli->query()) it just doesn't do anything. I know that the code I'm writing works because when I separate the second query it works every time.

[laserlight]

You may be able to use a join to combine the two queries into one, e.g.,

SELECT C1.Name, C1.ID, C2.Name, C2.Link, C2.abbr
FROM Categories C1, Categories C2
WHERE C1.ID=C2.belongsTo AND C1.ID<>C2.ID AND C1.Level=0
ORDER BY C1.Name, C2.Name

The C1.ID<>C2.ID clause might be redundant, but I am not sure with the given information.

[Loc]

Thanks, I've never used a join before so how would I use this query? How would I use it? It has to show all the Names of the fields with level 0 (And belongsTo -1) and with each of these fields the fields that have belongsTo equal to the ID. (Like seen in the code.)

[laserlight]

Assuming that it is correct, you would use it like any other query: execute it, then use a loop to retrieve the results. If it is correct, what it would select is "all the Names of the fields with level 0 (And belongsTo -1) and with each of these fields the fields that have belongsTo equal to the ID", except that "the Names of the fields with level 0" will be repeated for each corresponding "fields that have belongsTo equal to the ID".

[Loc]

Hmm it's not working I think.

$sql = "SELECT C1.Name, C1.ID, C2.Name, C2.Link, C2.abbr FROM Categories C1, Categories C2 WHERE C1.ID=C2.belongsTo AND C1.ID<>C2.ID AND C1.Level=0 ORDER BY C1.Name, C2.Name";

if($stmt = $mysqli->prepare($sql)) {
	if(!$stmt->execute()) {
		echo 'The query could not be executed: '.$stmt->error;
	} else {
		$stmt->bind_result($Name,$ID);

	while($stmt->fetch()) {
		echo "<li>" . $Name;


		echo "</li>";
	}
}

$stmt->close();
} else {
	 echo 'Incorrect Query: '.$mysqli->error;
}

This return:

<li></li>
<li></li>
<li></li>
<li></li>

So only 4 results are returned instead of the 6 in my table and the variable $Name is unidentified. I'm probably doing it wrong since both prepared queries and joins are new to me.

[laserlight]

You might want to provide the relevant database schema and the test data that you are using. It could be a problem with my suggested query, but it could also be a problem with what's actually in your test database.

[Loc]

Thanks for all your help, after long trying I managed to make this work.