Menu
Community /Pin to ProfileBookmark
Why COUNT() Value fails To Hold In mysqli_stmt_bind_result() ?
Php Folks,
Can you show me how to pass variable values from one function to another ?
Check this following code and let me know if I did alright to pass variable values from function to function or not.
How my script works ?
First when you load the page, you see a form. You’re in step: $_SESSION[‘form_step’] = ‘start’.
You fill form in and click “search” button. You’re in step:
$_SESSION[‘form_step’] = ‘midway’.
function result_count() gets triggered. This just grabs row count.
Then …
You’re in step: $_SESSION[‘form_step’] = ‘end’.
And function pagination () gets triggered. This grabs row data and displays them. Supposed to display with pagination like a SERP. (eg. Page 1234).
When you click the page numbers “1234 etc.” on the pagination section, then the function pagination() gets triggered again. This grabs row data and displays them for the new page (eg. page 2).
And so, when you click any page numbers on the pagination section, and go over to the next page (eg page 3) then the sql_query_1 does not trigger to COUNT anymore.
No more the function result_count() gets triggered.
Only the function pagination() gets triggerd to grab the row datas.
Only sql_query_2 here gets triggers.
PROBLEM:
I do not understand why my row count, using the COUNT(), from my first query “sql_query_1” fails inside the function result_count().
I tried echoing the row count again on function pagination() but no luck. I used the $row_count as GLOBAL to pass the value from the first function to the second. Even used as $_SESSION[‘row_count’] but no luck. What is wrong ?
QUESTIONS:
Q1. How to pass variable values from one function to another ? Through the $_SESSIONs like I did or by turning the variable into a GLOBAL one ?
I am not that good with any RETURN that returns values from one function to another. I’d appreciate someone showing me how to do this in php with a sample code, if they can.
Q2. Why $row_count & $_SESSION[‘row_count’] fails to echo or hold any values ?
Q3. Should I keep it as it is where on each page load (eg. page 2, page 3, etc.) only the 2nd query Sql_Query_2 gets triggered to display the row datas without doing any further ROW COUNTS or should I trigger the Sql_Query_1 aswell on each numbered page loads through the function result_count() ?
NOTE:
Bear in mind, I am a beginner and still at procedural style programming and prepared statements and mysqli. Not into oop or pdo yet. Would appreciate sample code demonstrations in them formats only.
Thank You!
I got my questions written on the comments of the code so you understand where I am struggling the whole night with hiccups!
““
<?php
error_reporting(E_ALL);
?>
<!DOCTYPE HTML”>
<html>
<head>
<meta name=”viewport” content=”width-device=width, initial-scale=1″>
</head>
<body>
<?php
if(isset($_SESSION[‘form_step’]))//Q1. WHY THIS ‘IF’ NEVER TRIGGERS WHEN PRESSING ‘ENTER’ AND LOADING PAGE ?
{
unset( $_SESSION[‘form_step’] );
session_unset();
session_destroy();
echo “Form Step:”; echo $_SESSION[‘form_step’];
echo “Line: 21”;
}
if(!session_id())
{
session_start();
$_SESSION[‘form_step’] = ‘start’;
echo “Line: 28<br>”;
}
if($_SESSION[‘form_step’] == ‘start’);
{
if(!isset($_GET[‘form_type’]) && empty($_GET[‘form_type’]))
{
die(“Invalid Form!”);
}
else
{
$_SESSION[‘form_type’] = $_GET[‘form_type’];
if(!isset($_GET[‘query_type’]) && empty($_GET[‘query_type’]))
{
die(“Invalid Query!”);
}
else
{
$_SESSION[‘query_type’] = $_GET[‘query_type’];
if(!function_exists($_SESSION[‘form_type’]))//eg. $_SESSION[‘search’]. This should trgger the search() function.
{
die(“Invalid Form!”);
}
else
{
$_SESSION[‘form_type’]();
}
}
}
}
function search()
{echo “Line: 63<br>”;//DELETE THIS LINE
?>
<form action=”<?php echo $_SERVER[‘PHP_SELF’];?>?form_type=<?php echo $_SESSION[‘form_type’];?>&query_type=<?php echo $_SESSION[‘query_type’];?>&form_step=midway&page_limit=2&page=1″ method=’post’ enctype=’plain/text’>
<?php
//Added ‘*’ (asterisk) to indicate the ‘Text Field’ is a ‘required’ one.
echo “<label for=”first_name”>First Name *:</label>
<input type=”text” name=”first_name” placeholder=”First Name” value = “”>”;?>
<br>
<?php
echo “<label for=”marital_status”>Marital Status *:</label>”;
echo “<select name=”marital_status”>”;
echo “<option value=”single”>Single</option>”;
echo “<option value=”married”>Married</option>”;
echo “</select>”;
echo “<br>”;
?>
<input type=”submit” name=”search” value=”Search”>
<?php
function result_count()
{
if(isset($_SESSION[‘form_step’]) && $_SESSION[‘form_step’] == ‘midway’)
{echo “Line: 84<br>”;//DELETE THIS LINE
//Connect to Database. (DB_SERVER, BD_USERNAME, DB_PASSWORD, DB_NAME).
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = mysqli_connect(“localhost”,”root”,””,”powerpage”);
$conn->set_charset(‘utf8mb4’); //Always set Charset.
if($conn === false)
{
die(“ERROR: Connection Error!. ” . mysqli_connect_error());
}
$sql_query_1 = “SELECT COUNT(id) FROM users WHERE first_name = ? AND marital_status = ?”;
$stmt_1 = mysqli_stmt_init($conn);
if(!mysqli_stmt_prepare($stmt_1,$sql_query_1))
{
die(“<pre>1a. Mysqli Prepare Failed!n”.mysqli_stmt_error($stmt_1).”n$sql_query_1</pre>”);
}
else
{
mysqli_stmt_bind_param($stmt_1,”ss”,$_POST[“first_name”],$_POST[“marital_status”]);
//Attempt to Execute the Prepared Statement.
if(!mysqli_stmt_execute($stmt_1))
{
//Close Connection.
mysqli_close($conn);
mysqli_close($conn);
die(“<pre>1b. Statement Execution failed!n”.mysqli_stmt_error($stmt_1).”nmysqli_stmt_bind_param</pre>”);
}
else
{
$result_1 = mysqli_stmt_bind_result($stmt_1,$row_count);
if(!mysqli_stmt_fetch($stmt_1))
{
die(“<pre>1c. Statement Fetching failed!</pre>”);
}
else
{
echo “Line 122:”; echo “$row_count<br>”; //Q2. Why this never echoes ? Why value blank ?
GLOBAL $row_count;
echo “Line 124:”; echo “$row_count<br>”; //Q2. Why this never echoes ? Why value blank ?
echo “Line 125:”; echo “Result 1:”; echo “$result_1<br>”; //This is echoesing as: “Line 124:Result 1:1”.
$_SESSION[“$row_count”] = $row_count;
echo “Line 128:”; $_SESSION[“$row_count”]; echo “<br>”; //Q2. Why this never echoes ? Why value blank ?
mysqli_stmt_close($stmt_1);
mysqli_close($conn);
}
}
}
$_SESSION[‘form_step’] = ‘end’;
pagination();
}
}
function pagination()
{
if(isset($_SESSION[‘form_step’]) && $_SESSION[‘form_step’] == ‘end’)
{echo “Line: 139<br>”;//DELETE THIS LINE
$page_number = $_GET[‘page’];
$result_per_page = $_GET[‘page_limit’];
$row_start = (($page_number * $result_per_page) – $result_per_page); //Offset (Row Number that ‘Starts’ on page).
$row_end = ($page_number * $result_per_page); //Max Result (Row Number that ‘Ends’ on page).
$previous_page = $page_number-1;
$next_page = $page_number+1;
echo “Row Start: $row_start<br>”;
echo “Row End: $row_end<br>”;
//Connect to Database. (DB_SERVER, BD_USERNAME, DB_PASSWORD, DB_NAME).
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = mysqli_connect(“localhost”,”root”,””,”powerpage”);
$conn->set_charset(‘utf8mb4’); //Always set Charset.
if($conn === false)
{
die(“ERROR: Connection Error!. ” . mysqli_connect_error());
}
$sql_query_2 = “SELECT * FROM users WHERE first_name = ? AND marital_status = ? LIMIT $row_start,$row_end”;
//$_SESSION[‘sql_query_2’] = $sql_query_2;
$stmt_2 = mysqli_stmt_init($conn);
if(!mysqli_stmt_prepare($stmt_2,$sql_query_2))
{
die(“<pre>2a. Mysqli Prepare Failed!n”.mysqli_stmt_error($stmt_2).”n$sql_query_2</pre>”);
}
else
{
mysqli_stmt_bind_param($stmt_2,”ss”,$_POST[“first_name”],$_POST[“marital_status”]);
//Attempt to Execute the Prepared Statement.
if(!mysqli_stmt_execute($stmt_2))
{
//Close Connection.
mysqli_close($conn);
mysqli_close($conn);
die(“<pre>2b. Statement Execution failed!n”.mysqli_stmt_error($stmt_1).”nmysqli_stmt_bind_param</pre>”);
}
else
{
$result_2 = mysqli_stmt_get_result($stmt_2);
mysqli_stmt_fetch($stmt_2);//Why it doesn’t work ? Check here: https://stackoverflow.com/questions/62353317/statement-fetching-fails-in-prepared-statement/62353402#
//if(!mysqli_fetch_assoc($result_2))//Was told in above link to replace this with below line.
if(!$result_2)
{
//Close Connection.
mysqli_close($conn);
mysqli_close($conn);
die(“<pre>2c. Statement Fetching failed!</pre>”);
}
else
{
//Grab total number of pages to paginate.
Global $row_count;
$row_count = $_SESSION[“$row_count”]; //Q2. Why this never echoes ? Why value blank ?
echo “Line 200:<br>”; echo “$row_count”; //Q2. Why this never echoes ? Why value blank ?
echo “Line 201:<br>”; echo $_SESSION[“$row_count”]; //Q2. Why this never echoes ? Why value blank ?
$total_pages = ceil($row_count/$result_per_page);
//$total_pages = ceil($result_1/$result_per_page);
echo “TOTAL PAGES: $total_pages<br><br>”;
//while($row = mysqli_fetch_assoc($result_2))
while($row = mysqli_fetch_array($result_2,MYSQLI_ASSOC))
{
//Retrieve Values.
$id = $row[“id”];
$first_name = $row[“first_name”];
$middle_name = $row[“middle_name”];
$surname = $row[“surname”];
$gender = $row[“gender”];
$marital_status = $row[“marital_status”];
$working_status = $row[“working_status”];
echo “Id: $id<br>”;
echo “First Name: $first_name<br>”;
echo “Middle Name: $middle_name<br>”;
echo “Surname: $surname<br>”;
echo “Gender: $gender<br>”;
echo “Marital Status: $marital_status<br>”;
echo “Working Status: $working_status<br>”;
}
mysqli_stmt_close($stmt_2);
mysqli_close($conn);
$i = 1;
while($i<=$total_pages)
{
if($i<$total_pages)
{
echo “<a href=’http://localhost/power.page/pagination_test_simple.php?form_type=”;?><?php echo $_SESSION[‘form_type’];?>&query_type=<?php echo $_SESSION[‘query_type’];?>&form_step=end&page_limit=2&page=<?php echo $i;?>’><?php echo ” $i “;?></a><?php
}
elseif($i==$page_number)
{
echo “<a href=’http://localhost/power.page/pagination_test_simple.php?form_type=”;?><?php echo $_SESSION[‘form_type’];?>&query_type=<?php echo $_SESSION[‘query_type’];?>&form_step=end&page_limit=2&page=<?php echo $i;?>’><?php echo “<b> $i </b>”;?></a><?php
}
elseif($page_number>$total_pages)
{
echo “<a href=’http://localhost/power.page/pagination_test_simple.php?form_type=”;?><?php echo $_SESSION[‘form_type’];?>&query_type=<?php echo $_SESSION[‘query_type’];?>&form_step=end&page_limit=2&page=<?php echo $previous_page;?>’><?php echo “<b> Previous </b>”;?></a><?php
}
$i++;
}
}
}
}
//die();
}
}
if($_SERVER[‘REQUEST_METHOD’] === ‘POST’)
{
if(isset($_POST[‘search’]))// && $_SESSION[‘form_step’] != ‘end’)
{
$_SESSION[‘form_step’] = ‘midway’;
echo “Line: 259<br>”;//DELETE THIS LINE
result_count();
}
}
}
?>
You call pagination pages like these as for now until further development:
localhost/power.page/pagination_test_simple.php?form_type=search&query_type=select&form_step=start&page_limit=2&page=1
localhost/power.page/pagination_test_simple.php?form_type=search&query_type=select&form_step=end&page_limit=2&page=2
localhost/power.page/pagination_test_simple.php?form_type=search&query_type=select&form_step=end&page_limit=2&page=3
Note the “form_step=” part.
You might like these. I will try getting into the habits:
Sign in
to post a comment