Jump to content


Check out our Community Blogs

uniqueideaman

Member Since 12 Oct 2016
Offline Last Active Jul 30 2018 01:41 PM
-----

Topics I've Started

Why mysqli_stmt_bind_result Fails ?

25 July 2018 - 03:39 AM

Php Gurus,
 
I have a mysql tbl "searching_histories".
It's columns are:
id|date_and_time |searched_keywords|browsed_page_url |browsed_page_type|referral_page|visitor_username|visitor_type
 
It's 3rd entry looks like this:
3 | 2018-07-25 01:13:32|Page Description |http://7search.com/|Information Page |NULL |admin123 |unhappy visitor
 
Now, I'm trying to write a php script that pulls matches from this mysql tbl.
The following url should have puleld that 3rd entry from my mysql tbl:
 
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); 
} 
?> 

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download