Menu
Php Gurus,
Check this prepared statement out using mysqli_stmt_get_result().
I am trying to build a pagination page.
I can’t figure-out why the statement fetching fails.
I do get echoed the row count and so the first Sql Query is working fine (sql_query_1) that gets the row count using the COUNT() function.
It’s the second Sql Query (sql_query_2) that is failing to pull the rows datas.
Also, I have added the db connection twice in the script flow. Like so:
[code]
//Connect to Database. (DB_SERVER, BD_USERNAME, DB_PASSWORD, DB_NAME).
$conn = mysqli_connect(“localhost”,”root”,””,”powerpage”);
$conn->set_charset(‘utf8mb4’); //Always set Charset.
if($conn === false)
{
die(“ERROR: Connection Error!. ” . mysqli_connect_error());
}
Is it necessary to add it a 2nd time on the spot that I did add it or should I delete it as adding it once on the file is ok ?
Why does my following line get triggered:
[code]
if(!mysqli_stmt_fetch($result_2))
{
die(“<pre>2c. Statement Fetching failed!</pre>”);
}
That is the big question!
Full Code:
[code]
<?php
error_reporting(E_ALL);
?>
<!DOCTYPE HTML”>
<html>
<head>
<meta name=”viewport” content=”width-device=width, initial-scale=1″>
</head>
<body>
<?php
if(!session_id())
{
session_start();
$_SESSION[‘form_step’] = ‘start’;
}
if($_SESSION[‘form_step’] != ‘end’);
{
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’]))
{
die(“Invalid Form!”);
}
else
{
$_SESSION[‘form_type’]();
}
}
}
function search()
{
?>
<form action=”<?php echo $_SERVER[‘PHP_SELF’];?>?form_type=<?php echo $_SESSION[‘form_type’];?>&query_type=<?php echo $_SESSION[‘query_type’];?>&form_step=end&page_limit=1&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
//$current_function = __FUNCTION__;
//echo $current_function;
if($_SERVER[‘REQUEST_METHOD’] === ‘POST’)
{
if(isset($_POST[‘search’]))// && $_SESSION[‘form_step’] != ‘end’)
{
//Connect to Database. (DB_SERVER, BD_USERNAME, DB_PASSWORD, DB_NAME).
$conn = mysqli_connect(“localhost”,”root”,””,”powerpage”);
$conn->set_charset(‘utf8mb4’); //Always set Charset.
if($conn === false)
{
die(“ERROR: Connection Error!. ” . mysqli_connect_error());
}
$form_step = $_GET[‘form_step’];
$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”;
echo “Row End: $row_end”;
$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);
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>”); //DELETE THIS LINE
}
else
{
echo “Row Count: $row_count<br>”;
//Connect to Database. (DB_SERVER, BD_USERNAME, DB_PASSWORD, DB_NAME).
$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 = ?”;
$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);
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);
if(!mysqli_stmt_fetch($result_2))
{
die(“<pre>2c. Statement Fetching failed!</pre>”); //DELETE THIS LINE
}
else
{
//Grab total number of pages to paginate.
$total_pages = ceil($result_1/$result_per_page);
//$total_pages = ceil($row_count/$result_per_page);
echo “TOTAL PAGES: $total_pages<br>”;
while($row = mysqli_fetch_array($result_2, MYSQLI_NUM)) //Use this if you use ‘$result = mysqli_stmt_get_result($stmt)’ instead of ‘mysqli_stmt_bind_result($stmt,$email)’.
{
//Retrieve Values.
$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 “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>”;
echo “Total Result Pages: $total_pages<br>”;
$i = 1;
while($i<=$total_pages)
{
if($i<$total_pages)
{
echo “<a href=’http://localhost/power.page/search_3.php?form_type=”;?><?php echo $_SESSION[‘form_type’];?>&query_type=<?php echo $_SESSION[‘query_type’];?>&form_step=end&page_limit=1&page=<?php echo $i;?>’><?php echo ” $i “;?></a><?php
}
elseif($i==$page_number)
{
echo “<a href=’http://localhost/power.page/search_3.php?form_type=”;?><?php echo $_SESSION[‘form_type’];?>&query_type=<?php echo $_SESSION[‘query_type’];?>&form_step=end&page_limit=1&page=<?php echo $i;?>’><?php echo “<b> $i </b>”;?></a><?php
}
elseif($page_number>$total_pages)
{
echo “<a href=’http://localhost/power.page/search_3.php?form_type=”;?><?php echo $_SESSION[‘form_type’];?>&query_type=<?php echo $_SESSION[‘query_type’];?>&form_step=end&page_limit=1&page=<?php echo $previous_page;?>’><?php echo “<b> Previous </b>”;?></a><?php
//session_destroy();
}
$i++;
}
}
}
}
}
}
}
}
}
}
}
}
?>
Anything I should know ?
If there are mistakes then kindly show fixes with code samples.
I am at beginner level. Still at procedural style programming. I do not understand objects and oop style. Bear that in mind when responding.
Thanks
EDIT:
I get error:
>
Warning: mysqli_stmt_fetch() expects parameter 1 to be mysqli_stmt, object given in C:xampphtdocstestpagination_test_SIMPLE.php on line 158
Line 158:
[code]
if(!mysqli_stmt_fetch($result_2))
Context:
[code]
$result_2 = mysqli_stmt_get_result($stmt_2);
if(!mysqli_stmt_fetch($result_2))
{
die(“<pre>2c. Statement Fetching failed!</pre>”); //DELETE THIS LINE
}