Prepared Statement going wrong

  • (5 Pages)
  • +
  • 1
  • 2
  • 3
  • Last »

68 Replies - 3758 Views - Last Post: 24 July 2015 - 09:12 AM Rate Topic: -----

#1 pfar54   User is offline

  • D.I.C Addict

Reputation: 1
  • View blog
  • Posts: 615
  • Joined: 30-April 15

Prepared Statement going wrong

Posted 20 July 2015 - 08:54 PM

I am creating a forum type section in a site I have. Right now I am at the point where if I go to a category and try to select on a topic. The part I am having trouble with is seeing all the posts.


I am having the roughest time getting this query to work. I have var dumped the
$cid
and
$tid
and I get the correct values. For some reason I cannot get past the prepare part and when I do after playing around with it, I get the else statement..
echo "<p>This topic does not exist.</p>


My db connection is correct and the info from my database are correct.



$con = mysqli_connect("localhost", "", "", "");
if (mysqli_connect_errno()) {
	printf("Connect failed: %s\n", mysqli_connect_error());
	exit();
}
$cid = $_GET['cid'];
$tid = $_GET['tid'];
$userid = ( isset( $_SESSION['user'] ) ? $_SESSION['user'] : "" );

//Prepared SELECT stmt to get forum topics
$stmt = $con->prepare("SELECT * FROM forum_topics WHERE `category_id`=? AND id=? LIMIT 1");
if (!$stmt || $con->error ) {
	die('Select topics prepare() failed: ' . htmlspecialchars($con->error));
}
if(!$stmt->bind_param('ii', $cid, $tid)) {
	die('Select topics bind_param() failed: ' . htmlspecialchars($stmt->error));
}
if(!$stmt->execute()) {
	die('Select topics execute() failed: ' . htmlspecialchars($stmt->error));
}
$stmt->store_result();
$numrows = $stmt->num_rows;
if($numrows == 1){
	echo "<table width='100%'>";
	if ( $_SESSION['user'] ) { 
		echo "<tr><td colspan='2'><input type='submit' value='Add Reply' onclick=\"window.location = 
	'forum_post_reply.php?cid=".$cid."$tid=".$tid."'\"> <hr />";
	} else {
		echo "<tr><td colspan='2'><p>Please log in to add your reply</p><hr /></td></tr>";
	}

	foreach($stmt as $row) {

		//Prepared SELECT stmt to get forum posts
		$stmt2 = $con->prepare("SELECT * FROM forum_posts WHERE `category_id`=? AND `topic_id`=?");
		//var_dump($stmt2);
		if ( !$stmt2 || !empty($con->error)) {
			die('Select forum posts prepare() failed: ' . htmlspecialchars($con->error));
		}
		//var_dump($con->error);
		if(!$stmt2->bind_param('ii', $cid, $tid)) {
			die('Select forum posts bind_param() failed: ' . htmlspecialchars($stmt2->error));
		}
		if(!$stmt2->execute()) {
			die('Select forum posts execute() failed: ' . htmlspecialchars($stmt2->error));
		}
		//while($row2 = mysqli_fetch_assoc($stmt2)){
		foreach($stmt2 as $row2) {	
			echo "<tr><td valign='top' style='border: 1px solid #000000;'>
			<div style='min-height: 125px;'>".$row['topic_title']."<br />
			by ".$row2['post_creator']." - " .$row2['post_date']. "<hr />" . $row2['post_content'] ."</div></td>
			<td width='200' valign='top' align='center' style='border: 1px solid #000000;'>User Info Here!</td></tr>
			<tr><td colspan='2'><hr /></td></tr>";
		}
	}
} else {
	echo "<p>This topic does not exist.</p>";
}




The following is what someone suggested and I tried it and it gets past the prepare and it outputs data, but if I on any topic I have, it outputs the first topic post no matter which topic I click on...

//echo "cid" .$cid;
//echo "tid" . $tid;
$topic_title = $row['topic_title'];
$topicCreator = $row['post_creator'];
$postDate = $row['post_date'];
$post_content = $row['post_content'];

$stmt = $con->prepare("SELECT topic_title, topic_creator FROM forum_posts AS p, forum_topics AS f WHERE f.category_id=? AND p.topic_id=? LIMIT 1");
if (!$stmt || $con->error ) { 
die('Select forum prepare() failed: ' . htmlspecialchars($con->error)); 
} 
if(!$stmt->bind_param('ii', $cid, $tid)) { 
die('Select forum bind_param() failed: ' . htmlspecialchars($stmt->error)); 
}
if(!$stmt->execute()) { 
die('Select forum execute() failed: ' . htmlspecialchars($stmt->error)); 
}
mysqli_stmt_bind_result($stmt, $post_content, $topic_title); 
$stmt->store_result(); 
echo $stmt->store_result(); 
$numrows = $stmt->num_rows; 
if($stmt->num_rows > 0){
echo "<table width='100%'>";
if ( $_SESSION['user'] ) {
    echo "<tr><td colspan='2'><input type='submit' value='Add Reply' onclick=\"window.location =
'forum_post_reply.php?cid=".$cid."$tid=".$tid."'\"> <hr />";
} else {
    echo "<tr><td colspan='2'><p>Please log in to add your reply</p><hr /></td></tr>";
}
while (mysqli_stmt_fetch($stmt)) {
echo "<tr><td valign='top' style='border: 1px solid #000000;'> 
<div style='min-height: 125px;'>".$topic_title."<br /> 
by " . $post_content ."</div></td> 
<td width='200' valign='top' align='center' style='border: 1px solid #000000;'>User Info Here!</td></tr> 
<tr><td colspan='2'><hr /></td></tr>";
} 
mysqli_stmt_close($stmt); 
} else { 
echo "<p>This topic does not exist.</p>"; 
}

?>
	</body>
</html>	



Does anyone see what I am doing wrong with either of these prepared statements?

Is This A Good Question/Topic? 0
  • +

Replies To: Prepared Statement going wrong

#2 Dormilich   User is offline

  • 痛覚残留
  • member icon

Reputation: 4286
  • View blog
  • Posts: 13,590
  • Joined: 08-June 10

Re: Prepared Statement going wrong

Posted 20 July 2015 - 11:53 PM

questions: is topic_id a primary key? if so, why query for the category as well?

additionally, if you query for a primary key there is absolutely no sense in adding LIMIT 1.
Was This Post Helpful? 0
  • +
  • -

#3 pfar54   User is offline

  • D.I.C Addict

Reputation: 1
  • View blog
  • Posts: 615
  • Joined: 30-April 15

Re: Prepared Statement going wrong

Posted 21 July 2015 - 06:06 AM

No, it is not. The `id` in my forum_posts db table is the primary key.
Was This Post Helpful? 0
  • +
  • -

#4 Dormilich   User is offline

  • 痛覚残留
  • member icon

Reputation: 4286
  • View blog
  • Posts: 13,590
  • Joined: 08-June 10

Re: Prepared Statement going wrong

Posted 21 July 2015 - 07:06 AM

then the first step ist testing your queries in a mysql client (e.g. MySQL Workbench, phpMyAdmin) to make sure they return the data you expect.
Was This Post Helpful? 0
  • +
  • -

#5 pfar54   User is offline

  • D.I.C Addict

Reputation: 1
  • View blog
  • Posts: 615
  • Joined: 30-April 15

Re: Prepared Statement going wrong

Posted 21 July 2015 - 07:10 AM

Prepared statement 1
There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem.

ERROR: Unknown Punctuation String @ 47
STR: =?
SQL: SELECT * FROM forum_topics WHERE `category_id`=? AND id=? LIMIT 1

SQL query: Documentation

SELECT * FROM forum_topics WHERE `category_id`=? AND id=? LIMIT 1

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? AND id=? LIMIT 1' at line 1 



Prepared statement 2
ERROR: Unknown Punctuation String @ 95
STR: =?
SQL: SELECT topic_title, topic_creator FROM forum_posts AS p, forum_topics AS f WHERE f.category_id=? AND p.topic_id=? LIMIT 1

SQL query: Documentation

SELECT topic_title, topic_creator FROM forum_posts AS p, forum_topics AS f WHERE f.category_id=? AND p.topic_id=? LIMIT 1

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? AND p.topic_id=? LIMIT 1' at line 1 

Was This Post Helpful? 0
  • +
  • -

#6 Dormilich   User is offline

  • 痛覚残留
  • member icon

Reputation: 4286
  • View blog
  • Posts: 13,590
  • Joined: 08-June 10

Re: Prepared Statement going wrong

Posted 21 July 2015 - 07:12 AM

for testing SQL statements you wouldóof courseóneed to replace the placeholders by the respective test values.
Was This Post Helpful? 0
  • +
  • -

#7 pfar54   User is offline

  • D.I.C Addict

Reputation: 1
  • View blog
  • Posts: 615
  • Joined: 30-April 15

Re: Prepared Statement going wrong

Posted 21 July 2015 - 07:17 AM

I did this query...

SELECT * FROM forum_topics WHERE `category_id`=1 AND id=3 LIMIT 1

And the result came up perfect.

and

SELECT topic_title, topic_creator FROM forum_posts AS p, forum_topics AS f WHERE f.category_id=? AND p.topic_id=? LIMIT 1


And got the correct result for that as well.
Was This Post Helpful? 0
  • +
  • -

#8 Dormilich   User is offline

  • 痛覚残留
  • member icon

Reputation: 4286
  • View blog
  • Posts: 13,590
  • Joined: 08-June 10

Re: Prepared Statement going wrong

Posted 21 July 2015 - 07:22 AM

View Postpfar54, on 21 July 2015 - 04:17 PM, said:

SELECT * FROM forum_topics WHERE `category_id`=1 AND id=3 LIMIT 1

can be simplified to
SELECT * FROM forum_topics WHERE id=3

since id is your primary key. (at least you said so previously)

another step, discard the whole code depending on num_rows and check if fetching the data works.


PS. personally I prefer PDO because itís much more straightforward than MySQLi Ö
Was This Post Helpful? 0
  • +
  • -

#9 pfar54   User is offline

  • D.I.C Addict

Reputation: 1
  • View blog
  • Posts: 615
  • Joined: 30-April 15

Re: Prepared Statement going wrong

Posted 21 July 2015 - 07:27 AM

If my queries are returning data than why would my first prepared statement not be shpwing anything?

Why would my second query not she showing the right topic creator and post? It is just running through the loop and putting the first post in my db?

This post has been edited by pfar54: 21 July 2015 - 07:54 AM

Was This Post Helpful? 0
  • +
  • -

#10 Dormilich   User is offline

  • 痛覚残留
  • member icon

Reputation: 4286
  • View blog
  • Posts: 13,590
  • Joined: 08-June 10

Re: Prepared Statement going wrong

Posted 21 July 2015 - 07:33 AM

Quote

If my queries are returning data than why would my first prepared statement not she shpwing anything?

how should I know that out of the blue?

thatís why I ask you to directly fetch the data to see, if there is a problem with data retrieval from the DB or if thereís a problem with the calls made to the mysqli objects. if you rely on num_rows alone, you canít tell which of those problems is responsible.
Was This Post Helpful? 0
  • +
  • -

#11 pfar54   User is offline

  • D.I.C Addict

Reputation: 1
  • View blog
  • Posts: 615
  • Joined: 30-April 15

Re: Prepared Statement going wrong

Posted 21 July 2015 - 08:00 AM

With my first query I get this error line from my code..

Select forum posts prepare() failed:

Found here:
$stmt2 = $con->prepare("SELECT * FROM forum_posts WHERE `category_id`=? AND `topic_id`=?");
		//var_dump($stmt2);
		if ( !$stmt2 || !empty($con->error)) {
			die('Select forum posts prepare() failed: ' . htmlspecialchars($con->error));
		}



I'm getting 10 different post blocks instead of the one that should be in it.Also nothing is being outputted into it.

If I var_dump $stmt2, I get:

object(mysqli_stmt)#10 (10) { ["affected_rows"]=> int(0) ["insert_id"]=> int(0) ["num_rows"]=> int(0) ["param_count"]=> int(2) ["field_count"]=> int(6) ["errno"]=> int(0) ["error"]=> string(0) "" ["error_list"]=> array(0) { } ["sqlstate"]=> string(5) "00000" ["id"]=> int(2) }

This post has been edited by pfar54: 21 July 2015 - 08:03 AM

Was This Post Helpful? 0
  • +
  • -

#12 ArtificialSoldier   User is offline

  • D.I.C Lover
  • member icon

Reputation: 2800
  • View blog
  • Posts: 8,144
  • Joined: 15-January 14

Re: Prepared Statement going wrong

Posted 21 July 2015 - 09:48 AM

That's not an error, maybe $con->error is not empty for whatever reason, but that's not an error. Your original problems might have to do with using store_result, did you look up that function and see what it does?
Was This Post Helpful? 0
  • +
  • -

#13 Dormilich   User is offline

  • 痛覚残留
  • member icon

Reputation: 4286
  • View blog
  • Posts: 13,590
  • Joined: 08-June 10

Re: Prepared Statement going wrong

Posted 21 July 2015 - 10:45 AM

Quote

Select forum posts prepare() failed:

if you donít execute the statement with the bound values, you canít have a result.
Was This Post Helpful? 0
  • +
  • -

#14 pfar54   User is offline

  • D.I.C Addict

Reputation: 1
  • View blog
  • Posts: 615
  • Joined: 30-April 15

Re: Prepared Statement going wrong

Posted 21 July 2015 - 10:45 AM

View PostArtificialSoldier, on 21 July 2015 - 09:48 AM, said:

That's not an error, maybe $con->error is not empty for whatever reason, but that's not an error. Your original problems might have to do with using store_result, did you look up that function and see what it does?


I was told that it would help pass the information down to my query $stmt2 from $stmt.
Was This Post Helpful? 0
  • +
  • -

#15 Dormilich   User is offline

  • 痛覚残留
  • member icon

Reputation: 4286
  • View blog
  • Posts: 13,590
  • Joined: 08-June 10

Re: Prepared Statement going wrong

Posted 21 July 2015 - 10:47 AM

maybe thereís something wrong in the code passing down that information.
Was This Post Helpful? 0
  • +
  • -

  • (5 Pages)
  • +
  • 1
  • 2
  • 3
  • Last »