Hiya,
I know it is possible to auto generate prepared statements based on table column names and numbers. But how to achieve it ?
I have many tables and I want to allow visitors to be able to search these tables.
Now, if I have 20 different tables, I do not want to be writing 20 different prepared statements as that would make the script very long and would slow down the page if tonnes of visitors simultaneously use the page from all across the globe.
And so, I want to only have one set of prepared statement based on which table the visitor selects on the webform that he wants to search.
A typical example:
Imagine these 2 tables:
1
$link_crawls_table_columns = array('id','date_and_time','domain','url','title','header','meta_keyword','meta_description',);2
$link_submits_table_columns = array('id','date_and_time','url','header','description','keyword','keyphrase');Now, for these 2 tables, I am having to MANUALLY write the prepared statements like this as each tables' column names and numbers are different:
if(!mysqli_stmt_prepare($stmt,$query))
{
echo 'Mysqli Error: ' .mysqli_stmt_error($stmt); //DEV Mode line.
echo '<br>';
echo 'Mysqli Error No: ' .mysqli_stmt_errno($stmt); //DEV Mode line.
}
else
{
if($index=='link_crawls')
{
//8 inputs.
mysqli_stmt_bind_param($stmt,"ssssssss",$search,$search,$search,$search,$search,$search,$search,$search);
}
else //$index=='link_submits'.
{
//7 inputs.
mysqli_stmt_bind_param($stmt,"sssssss",$search,$search,$search,$search,$search,$search,$search);
}
}Now imagine, I am writing like this for 20 tables!
Code will be too long!
Anyways, look how I am echoing keyword search results from these 2 tables:
while($row = mysqli_fetch_array($result,MYSQLI_ASSOC))
{
if($index=='link_crawls')
{
$id = $row['id'];
$date_and_time = $row['date_and_time'];
$domain = $row['domain'];
$url = $row['url'];
$title = $row['title'];
$header = $row['header'];
$meta_keyword = $row['meta_keyword'];
$meta_description = $row['meta_description'];
echo "<br>";
}
else
{
$id = $row['id'];
$date_and_time = $row['date_and_time'];
$url = $row['url'];
$header = $row['header'];
$description = $row['description'];
$keyword = $row['keyword'];
$keyphrase= $row['keyphrase'];
echo "<br>";
}
}Now imagine, I am writing like this for 20 tables!
Too long the code will become!
You get my point ? Need to shorten the code!
That is why, I have to write php code for the code to check which table is getting selected (dropdown or checkbox) on the html form that the visitor wants to search and then check the array associated with that table for the table column names and the column numbers as these 2 data is needed to auto generate the prepared statements.
But I do not know how to do this auto generation of prepared statements. I did a little bit using beginner/intermediate level php programming, which I showed you on my above code and I am showing you some more on my below code. Look:
$table = !EMPTY($_POST['table'])?$_POST['table']:(!EMPTY($_GET['table'])?$_GET['table']:'links_crawls');
$total_table_columns = count(${$table}); echo '<br>';
$cols = array();
for($i=0;$i!==$total_table_columns;$i++)
{
$cols[] = $col_[$i] = ${$table}[$i]; echo '<br>';
}
if($match == 'exact')
{
$sql_count = "SELECT * from $table WHERE $col_[0] = ?";
$sql = "SELECT * from $table WHERE $col_[0] = ?";
for($i=1;$i!==$total_table_columns;$i++)
{
$sql_count .= " OR $col_[$i] = ?";
$sql .= " OR $col_[$i] = ?";
}
$sql .= " OR $col_[$i] = ?";
}
else //Generate Sql for FUZZY MATCH
{
$sql_count = "SELECT * from $table WHERE $col_[0] LIKE ?";
$sql = "SELECT * from $table WHERE $col_[0] LIKE ?";
for($i=1;$i!==$total_table_columns;$i++)
{
$sql_count .= " OR $col_[$i] LIKE ?";
$sql .= " OR $col_[$i] LIKE ?";
}
$sql .= " ORDER BY DESC LIMIT $limit OFFSET $offset";
}Above, I have semi auto generated the table column names by taking the names from the respective array that holds the table column names. And, I am also extracting the number of columns that exist in the table as I will need it for the "s". talking about this part:
mysqli_stmt_bind_param($stmt,"ss",$input_1,$input_2);