Menu
Community /Pin to ProfileBookmark
@NogDogAug 03.2018 — #Define "doesn't work". Is there an error, or just not getting the result you expect? If the former, what error do you get (maybe have to check the php logs)? If the latter, try echoing out the query you are using, and double check that any variable you are binding to it have the expected values, and then perhaps try running it directly against your DB with those values to see if it does what you really think it does.
In any case, do not dump your entire file with mostly unrelated code to the actual problem here. Also, I'm a big fan of typing SQL across multiple lines instead of one long line. (A newline character takes the same amount of memory as a space.)
PS: Just using hypothetical variable names above for limit and offset -- be sure to use the actual names. ?
Why mysqli_stmt_bind_result Fails ?
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 |
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:
[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: $errorn”;
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);
}
?>
Sign in
to post a comment6 Comments(s) ↴
0
@NogDogAug 03.2018 — #Define "doesn't work". Is there an error, or just not getting the result you expect? If the former, what error do you get (maybe have to check the php logs)? If the latter, try echoing out the query you are using, and double check that any variable you are binding to it have the expected values, and then perhaps try running it directly against your DB with those values to see if it does what you really think it does.In any case, do not dump your entire file with mostly unrelated code to the actual problem here. Also, I'm a big fan of typing SQL across multiple lines instead of one long line. (A newline character takes the same amount of memory as a space.)
``<i>
</i>$query_2 = "SELECT
id,
date_and_time,
visitor_username,
visitor_type,
searched_keywords,
browsed_page_url,
browsed_page_type,
referral_page_url
FROM searching_histories
WHERE
visitor_type = ? AND
browsed_page_url = ? AND
browsed_page_type = ?
ORDER BY id LIMIT ? OFFSET ?"; <i>
</i>`</CODE>
Which, now that I just edited that example, I suspect that you are not allowed to use bound parameters for limit and offset, but I'm not 100% sure. (In PostgreSQL it won't let you, but I've not used MySQL in a long time now, so am not entirely certain.) You may want to do something like:
<CODE>`<i>
</i>". . .
ORDER BY id LIMIT ".intval($limit)." OFFSET ".intval($offset);<i>
</i>``PS: Just using hypothetical variable names above for limit and offset -- be sure to use the actual names. ?