Why Mysqli_stmt_bind_result Fails ?

phpsane · July 25, 2018

Php Gurus,

I have a mysql tbl "searching_histories".

It's columns are:

It's 3rd entry looks like this:

Now, I'm trying to write a php script that pulls matches from this mysql tbl.

The following url should have pulled that 3rd entry from my mysql tbl:

http://localhost/test/visitors_stats.php?visitor_type=unhappy_visitors&browsed_page_type=Information%20Page&browsed_page_url=http://7search.com/&links_per_page=25&page_number=

But it does not.

The page fails to pull the data. The page shows like this with hardly any data pulled:

"Search Result:

Visitor Type:

Browsed Page:

Browsed Page Type:

Matches: 0

0 Result Found ...

Submission Date & Time in California Usa

Visitor Username:

Visitor Type:

Keywords Search:

Browsed Page:

trailers_stats.php?browsed_page=

Browsed Page Type:

Referral Page:

trailers_stats.php?referral_page="

Notice, the page shows "matches:0". There should be a match on the 3rd entry (mysql tbl).

Anyway, here is my full code:

<?php 
 
//Required PHP Files. 
include 'config.php'; 
include 'header.php'; 
include 'account_header.php'; 
 
if (!$conn) 
{ 
$error = mysqli_connect_error(); 
$errno = mysqli_connect_errno(); 
print "$errno: $error\n"; 
exit(); 
} 
else 
{  
    //Get the Page Number. Default is 1 (First Page). 
$page_number = $_GET["page_number"]; 
if ($page_number == "") 
{ 
$page_number = 1; 
} 
 
$visitor_type = $_GET["visitor_type"]; 
$browsed_page_url = $_GET["browsed_page_url"]; 
$browsed_page_type = $_GET["browsed_page_type"]; 
 
echo "$visitor_type";//delete these as getting echoed
echo "$browsed_page_url";//delete these as getting echoed
echo "$browsed_page_type";//delete these as getting echoed
 
$links_per_page = $_GET["links_per_page"]; 
if ($links_per_page == "") 
{ 
$links_per_page = 25; 
} 
 
$max_result = 100;  
//$offset = ($page_number*$links_per_page)-$links_per_page; 
$offset = ($page_number-1)*$links_per_page; 
 
//$query_1 = "SELECT COUNT(*) FROM searching_histories WHERE visitor_type = ?"; 
$query_1 = "SELECT COUNT(*) FROM searching_histories WHERE visitor_type = ? AND browsed_page_url = ? AND browsed_page_type = ? ORDER BY id LIMIT ? OFFSET ?"; 
$stmt_1 = mysqli_prepare($conn,$query_1); 
//mysqli_stmt_bind_param($stmt_1,'sss',$visitor_type,$browsed_page_url,$browsed_page_type); 
mysqli_stmt_bind_param($stmt_1,'sssii',$visitor_type,$browsed_page_url,$browsed_page_type,$links_per_page,$offset); 
mysqli_stmt_execute($stmt_1); 
$result_1 = mysqli_stmt_bind_result($stmt_1,$matching_rows_count); 
mysqli_stmt_fetch($stmt_1); 
mysqli_stmt_free_result($stmt_1); 
 
$total_pages = ceil($matching_rows_count/$links_per_page); 
$query_2 = "SELECT id,date_and_time,visitor_username,visitor_type,searched_keywords,browsed_page_url,browsed_page_type,referral_page FROM searching_histories WHERE visitor_type = ? AND browsed_page_url = ? AND browsed_page_type = ? ORDER BY id LIMIT ? OFFSET ?"; 
$stmt_2 = mysqli_prepare($conn,$query_2); 
mysqli_stmt_bind_param($stmt_2,'sssii',$visitor_type,$browsed_page_url,$browsed_page_type,$links_per_page,$offset); 
mysqli_stmt_execute($stmt_2); 
$result_2 = mysqli_stmt_bind_result($stmt_2,$id,$date_and_time,$visitor_username,$visitor_type,$searched_keywords,$browsed_page_url,$browsed_page_type,$referral_page); 
mysqli_stmt_fetch($stmt_2); 
 
echo "$visitor_username";//WHY ARE NOT THESE GETTING ECHOED ?
echo "$visitor_type";//WHY ARE NOT THESE GETTING ECHOED ?
echo "$searched_keywords";//WHY ARE NOT THESE GETTING ECHOED ?
 
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional/EN"> 
<html> 
<head> 
<meta content="text/html; charset=ISO-8859-1" http-equiv=" content-type"> 
<title><?php echo "Search Result:<br> Visitor Type: $visitor_type<br> Browsed Page: $browsed_page_url<br> Browsed Page Type: $browsed_page_type<br> Matches: $matching_rows_count<br>"; ?></title> 
</head> 
<body> 
<br> 
<p align="center"><span style="font-weight:bold;"><?php echo "Search Result:<br> Visitor Type: $visitor_type<br> Browsed Page: $browsed_page_url<br> Browsed Page Type: $browsed_page_type<br> Matches: $matching_rows_count<br>"; ?></span></align> 
<br> 
<br> 
<table width="1500" border="0" cellpadding="5" cellspacing="2" bgcolor="#666666"> 
<?php if(!$stmt_2) 
{ 
    ?> 
<tr> 
    <td bgcolor="#FFFFFF">No record found! Try another time.</td> 
</tr> 
    <?php 
} 
else 
{ 
if(($offset+1)<=$max_result) 
{ 
printf("<b> %d Result Found ...</b>\n",$matching_rows_count); ?><br> 
<br> 
<b>Submission Date & Time in <?php echo "$server_time" ?></b><br><?php printf("%s",$date_and_time); ?><br> 
<br> 
<br> 
<b>Visitor Username:</b><br>
<?php printf("%s",$visitor_username); ?>
<br> 
<br> 
<b>Visitor Type:</b><br>
<?php printf("%s",$visitor_type); ?>
<br> 
<br> 
<b>Keywords Search:</b><br>
<?php printf("%s",$searched_keywords); ?>
<br> 
<br> 
<b>Browsed Page:</b><br>
<?php printf("%s","<a href=\"trailers_stats.php?browsed_page=$browsed_page_url\">trailers_stats.php?browsed_page=$browsed_page_url</a>"); ?> 
<br> 
<br> 
<b>Browsed Page Type:</b><br>
<?php printf("%s",$browsed_page_type); ?>
<br> 
<br> 
<b>Referral Page:</b><br>
<?php printf("%s","<a href=\"trailers_stats.php?referral_page=$referral_page\">trailers_stats.php?referral_page=$referral_page</a>"); ?> 
<br> 
<br> 
<?php 
    //Use this technique: http://php.net/manual/en/mysqli-stmt.fetch.php 
    while(mysqli_stmt_fetch($stmt_2)) 
    { 
?> 
<b>Submission Date & Time in <?php echo "$server_time" ?></b><br><?php printf("%s",$date_and_time); ?><br> 
<br> 
<br>
<b>Visitor Username:</b><br>
<?php printf("%s",$visitor_username); ?>
<br> 
<br> 
<b>Visitor Type:</b><br>
<?php printf("%s",$visitor_type); ?>
<br> 
<br>
<b>Keywords Search:</b><br>
<?php printf("%s","<a href=\"trailers_stats.php?keywords=$keywords_search\">trailers_stats.php?keywords=$keywords_search</a>"); ?> 
<br> 
<br> 
<b>Browsed Page:</b><br>
<?php printf("%s","<a href=\"trailers_stats.php?browsed_page=$browsed_page\">trailers_stats.php?browsed_page=$browsed_page</a>"); ?> 
<br> 
<br> 
<b>Browsed Page Type:</b><br>
<?php printf("%s",$browsed_page_type); ?>
<br> 
<br> 
<b>Referral Page:</b><br>
<?php printf("%s","<a href=\"trailers_stats.php?referral_page=$referral_page\">trailers_stats.php?referral_page=$referral_page</a>"); ?> 
<br> 
<br> 
<tr name="pagination"> 
<td colspan="10" bgcolor="#FFFFFF"> Result Pages: 
<?php              
if($page_number<$total_pages) 
{ 
for($i=1;$i<=$total_pages;$i++) //Show Page Numbers in Serial Order. Eg. 1,2,3.
echo "<a href=\"{$_SERVER['PHP_SELF']}?user=$user&page_number={$i}\">{$i}</a> "; 
}  
else 
{ 
for($i=$total_pages;$i>=1;$i--) //Show Page Numbers in Reverse Order. Eg. 3,2,1.
echo "<a href=\"{$_SERVER['PHP_SELF']}?user=$user&page_number={$i}\">{$i}</a> "; 
} 
?> 
</td> 
</tr> 
<?php 
} 
} 
}
?>   
</table>  
<br> 
<br> 
<p align="center"><span style="font-weight:bold;"><?php echo "Search Result:<br> Visitor Type: $visitor_type<br> Browsed Page: $browsed_page_url<br> Browsed Page Type: $browsed_page_type<br> Matches: $matching_rows_count<br>"; ?></span></align> 
<br> 
</div> 
<br> 
</body> 
</html> 
<?php 
//Free Result Set. 
mysqli_stmt_free_result($stmt_2); 
//Close Statement Connection. 
mysqli_stmt_close($stmt_2); 
//Close Database Connection. 
mysqli_close($conn); 
} 
?>

Edited July 25, 2018 by phpsane

requinix · July 25, 2018

Is it "unhappy visitor" or "unhappy_visitors"?

phpsane · July 25, 2018

5 minutes ago, requinix said:

Is it "unhappy visitor" or "unhappy_visitors"?

Thanks Requinix!

Your question solved the problem.

This url is now pulling the tbl data:

http://localhost/test/visitors_stats.php?visitor_type=unhappy visitor&browsed_page_type=Information Page&browsed_page_url=http://7search.com/&links_per_page=25&page_number=

The entry was "unhappy visitor" and so I never should have added "unhappy_visitor" in the url.

phpsane · July 25, 2018

Requinix,

One question. More than 8 mnths back, I got banned here (for asking questions too frequent, I think). Today, I find my account active. What is going on ?

Anyway, I am glad I can roam here again.

I hope McGuyver does not put you upto banning me again just because I ask a lot php questions that newbies usually do and forget to do. Lol!

Edited July 25, 2018 by phpsane

requinix · July 25, 2018

Yeah, I don't know how you got back. Probably something to do with when we upgraded the forums a couple months ago.

Consider this a lucky second chance. You're on a short lease, okay? One thread at a time, don't ask the same questions over and over, and do whatever it takes to not get on people's nerves.

mac_gyver · July 25, 2018

buried somewhere in that mess of mysqli code is a SELECT COUNT(*) ... query that has ORDER BY and LIMIT terms in it. this is incorrect.

to do pagination, you need two similar queries. both queries need the same FROM/JOIN/WHERE/GROUP BY/HAVING terms. the query to get the count of matching rows needs to SELECT COUNT(*). the query to get the data needs to SELECT a list of column names AND have the ORDER BY and LIMIT terms.

the common FROM/JOIN/WHERE/GROUP BY/HAVING syntax and corresponding data values for the queries should be built in a php variables, a string for the sql syntax and an array for the data, so that you are not repeating logic in your code, then simply use the resulting variables in both queries.

you should also get the total number of pages first, then use it to test/limit the requested page number, so that you are not running the data retrieval query for a set of rows that don't exist.

lastly, if you switch to the much simpler and more consistent php PDO extension, about half of the database statements will go-a-way and it will be easier to use the common sql syntax and corresponding data in the two queries needed for pagination.

phpsane · July 25, 2018

18 minutes ago, mac_gyver said:

buried somewhere in that mess of mysqli code is a SELECT COUNT(*) ... query that has ORDER BY and LIMIT terms in it. this is incorrect.

to do pagination, you need two similar queries. both queries need the same FROM/JOIN/WHERE/GROUP BY/HAVING terms. the query to get the count of matching rows needs to SELECT COUNT(*). the query to get the data needs to SELECT a list of column names AND have the ORDER BY and LIMIT terms.

the common FROM/JOIN/WHERE/GROUP BY/HAVING syntax and corresponding data values for the queries should be built in a php variables, a string for the sql syntax and an array for the data, so that you are not repeating logic in your code, then simply use the resulting variables in both queries.

you should also get the total number of pages first, then use it to test/limit the requested page number, so that you are not running the data retrieval query for a set of rows that don't exist.

lastly, if you switch to the much simpler and more consistent php PDO extension, about half of the database statements will go-a-way and it will be easier to use the common sql syntax and corresponding data in the two queries needed for pagination.

Thanks MacGuyver!

That is helpful.

But, if it is not too much to ask. How-about you show me a code sample of what you just advised ? By all means, you're welcome to edit my code and then paste here. Remember that, future newbies would flow here and benefit from our samples.

You know how it is with newbies. They think they understand you but actually they quite don't. And a sample code always mispells anything they overlooked.

That way, I can quit mysqli and procedural style faster and migrate to pdo and oop style like everyone suggested last yr over here and on other forums. Otherwise, I will have to hang onto mysqli just a little bit longer.

pdodelusions.com they suggested.

Oh btw (by the way), I missed you you old duffer! Meaning, I missed your replies like this one. I hope you will frequent my threads like last yr. And, don't put Requinix upto ban me again just because I ask a lot of questions or because I called you a "duffer" here.

I look forward to having you on my side when I migrate to pdo.

Just one question. Do you reckon I should quit mysqli asap and start on pdo ? Or, do you reckon I should finish my current projects (only 2) and then start on pdo ? Might aswell finish my projects than start all over with pdo. Right ?

Anyway, I'm going back to work again. Building a searchengine that tracks and publicizes who is browsing what. So now, you (a public) can do a Username search (eg. my Username) and browse what keywords I am searching, which links I am clicking, which link I am currently at and mssg me (if you're a competition of the link I am currently at). You can also do a keyword search and see which links have been clicked by which Usernames. Finally, you can do a url search and see which Usernames have been browsing it under which keywords. And a whole lot more. Have a look at my code again and you will see how much I have progressed. This is an interesting project. Keep tabs on it.

Edited July 25, 2018 by phpsane

mac_gyver · July 25, 2018

the reason for the previous ban here was due to having multiple accounts. this is the UI man/unique idea man user that has gotten himself banned on several forums.

which reminds me, the explanation posted above for the two queries needed to do this was already explained to you, which you acknowledged reading. if you are not going to learn from or follow the advice being given in the replies, just stop. you are wasting the forum member's time.

ginerjm · July 25, 2018

If you took the time to look in the manual under the PDO interface and looked at the examples for using the pdo prepare function and the execute function you could learn a lot without having to risk trouble.

phpsane · July 25, 2018

20 minutes ago, ginerjm said:

If you took the time to look in the manual under the PDO interface and looked at the examples for using the pdo prepare function and the execute function you could learn a lot without having to risk trouble.

I do not know pdo atall.

ginerjm · July 25, 2018

We know that. But - you asked someone here to do YOUR work for you when what you s/b doing is following my suggestion and go RTFM! What is so hard about that?

phpsane · July 25, 2018

3 hours ago, ginerjm said:

We know that. But - you asked someone here to do YOUR work for you when what you s/b doing is following my suggestion and go RTFM! What is so hard about that?

It's called seeing samples and learning it faster. Faster through the code samples. English is not my first language. Sometimes I think I understood someone but actually I understood quite the opposite. Hence, code samples help me understand the right way.

Ginerjm,

Why is it that on some forums you are impatient and swear at me when I expect you not to while on other forums you show a lot of patience and restraint to my questions when I half expect you to be impatient ? On those other forums where you have shown patience, I used other Usernames.

One day, I might point my threads and your replies out to you and you will say "Ah! So, that was you ?". On that forum, you and Nog Dog came running at nearly every one of my threads and saved my day! You will then feel embarrassed for your languages here. Nevermind. Don't bother replying as I don't want to turn this thread into a chit chat. And, Mac Guyver might ban us both. Or, Requinix. And so, let's keep it civiized.

I am keeping the thread open incase I encounter more problems.

Take care.

Don't bother replying.

Edited July 25, 2018 by phpsane

benanamen · July 25, 2018

FYI Experts, this is the infamous uniqueideman posing as yet another user after being banned and shunned on numerous forums including being banned on phphelp.com just this morning. OP is a help vampire. Stop feeding him and he will go away.

Edit: Just saw @mac_gyver already outed him.

Edited July 25, 2018 by benanamen

gizmola · July 25, 2018

Hi phpsane:

This is simply some friendly advice. I don't know of a single experienced developer here that uses mysqli over PDO. There are many reasons for this which I'm not going to go into.

I know that you are not a native english speaker and I appreciate the difficulty involved in that. Sometimes your posts imply that you are either in too big of a hurry or dismissive of advice.

Even if you have a large project that you need to maintain which was entirely done with mysqli, you could still use PDO and start a migration to it by coding new features, or fixing bugs using PDO. They can both be used in the same script and won't interfere with each other if you understand how to use them.

Fortunately there is an excellent "unofficial" PDO manual/tutorial you can read, that is full of code examples. The same author has a tutorial on using mysqli "properly".

I'll conclude with a comment and some constructive criticism:

Nobody wants to read 100 lines of unindented code.
- It looks bad, it's a pain to wade through
- it is not easy to see the logical flow
The people answering your questions typically are very experienced programmers. They help people as a service to the community, for free!
We enjoy seeing people progress as developers.
- You should be at this point, applying DRY.
  - Where is your code structured?
  - Why aren't you using functions and/or classes in your code to improve your code quality and reusability?
  - There are high quality component libraries and frameworks of all types (full stack, micro, component based) that will drastically improve the quality of your projects

People would like to see community members evolve and become better developers. It is painful and frustrating to see someone churn out the same type of low quality code over and over. I would say that the consensus is that you have demonstrated the intellectual capacity for this, so the question is, why are we still seeing code from you that reflects the way novice developers coded a decade ago?

ginerjm · July 26, 2018

FYI - the official PHP manual is produced in multiple languages.

phpsane · July 28, 2018

Gizmilo,

Don't worry. I know about pdodelusions.com for over a yr now as Benanamen suggested it. I just thought best finish mysqli first before getting into pdo.

Benanamen is the one who goes around forums trailing me to get me banned as if he has some vendetta against me for telling him that I struggle to remember his Username and if I could call him "Banana Man" instead in codingforums.com. Lol!

One thing I will do though. When I jump into pdo, I will start with pdodelusions.com. And, I will see if I can get some tutorial suggestions from Requinix first and then Mac Guyver. Requinix is a little less strict and so I will try with him first.

As for my 2 projects, one is complete and the other is 90% complete and so I will complete the remaining 10% and then start on pdodelusions.com and make Benanamen happy. Ofcourse, I will ask questions here or elsewhere for that 10% where I get stuck.

Thanks

Edited July 28, 2018 by phpsane

gizmola · July 29, 2018

What's more important than mysqli vs PDO in my post is my comments about code structure and indentation.

benanamen · July 30, 2018

OK UIman AKA phpsane,

This is where I was referring to that you are still calling people by your pet names. The admins name is gizmola, not Gizmilo. Don't bother derailing the thread with your long winded posts in response. Just pointing it out since you claimed you are not doing this anymore. This is not the only recent instance. Saying you cannot remember my or anyone else's correct username is so lame. Its right there on every single post. Blaming others for you getting banned is just as lame. You are the sole reason you have been banned so many times.

Sign In

Why Mysqli_stmt_bind_result Fails ?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information