two consecutive queries with mysqli

[tHud]

HI

I'm just trying to learn some php/mysqli and I'm not sure how to run two consecutive queries.

 

I need to call a function which will... a) get a result (howmany) then apply that to a second query e.g. a LIMIT $howmany

 

Something like this...

// if form has been submitted, do stuff

if (array_key_exists('build', $_POST)) { 

	$conn->autoCommit(false);

	doStuff($a, $b);

	$conn->commit();

}





function doStuff($a, $b){

$sql1 = "SELECT howmany FROM news WHERE id = ?";
$stmt1 = $conn->stmt_init();
$stmt1->prepare($sql1);
$stmt1->bind_param('i', $a);
$stmt1->execute();
$stmt1->bind_result($howmany);
$stmt1->fetch();
$stmt1->close();



$sql2 = "SELECT STUFF LIMIT $howmany";

$stmt2 = $conn->stmt_init();

if ($stmt2->prepare($sql2)) {
	
	$stmt2->bind_param('i', $catNumber);
	$stmt2->bind_result($id, $subject, $howmany);

	$ok	 = $stmt2->execute();

    while ($stmt2->fetch()) {

	// do stuff

	}

}

I am aware that this looks awful but that's why I'm here

 

How could I go about doing this properly?

 

Sorry if there is insufficient info - I'll try to correct that as I get responses (if I get any )

 

Thank you.

[lemmin]

You can actually do this:

"SELECT STUFF FROM table LIMIT (SELECT howmany FROM news WHERE id = ? LIMIT 1)"

Edited April 24, 2013 by lemmin

[tHud]

Hi,

Thanks very much for the response.

 

I've tried it but I guess I'm going wrong somewhere.

$sql = "SELECT id, subject FROM newsdat WHERE category = ? ORDER BY id DESC LIMIT (SELECT howmany FROM newscat WHERE id = ?)";

$stmt = $conn->stmt_init();

if ($stmt->prepare($sql)) {
	
	$stmt->bind_param('i', $catNumber);

// I also tried this...
// $stmt->bind_param('ii', $catNumber, $catNumber);

	$stmt->bind_result($id, $subject);

I'm quite lost...

[lemmin]

I'm sorry, you apparently can't do that in MySQL. My mistake.

 

I supposed you DO need to query for the LIMIT value first. Was there an error returned by the code in your first post?

 

It's hard to tell since you didn't post a real query in your first post, but I think the problem is with the parameters. If you use your original code and change these lines, it might fix the problem:

$sql2 = "SELECT id, subject FROM newsdat WHERE category = ? ORDER BY id DESC LIMIT ?";
$stmt2->bind_param('ii', $catNumber, $howmany);