How To Auto Generate Prepared Statements Based On Array Values?

[idrisalisylbd]

Hi,

How to auto generate prepared statements based on table column names and numbers ?

You see, I am creating a search page with pagination. Something like google.
Now, I have many tables and I want to allow visitors to be able to search these tables. Imagine these tables are like indexes. So, I have a users table, a members table, 2 links table where one is crawled links and one is submitted links. And so on.
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.
If you are confused then imagine I got 2 tables which visitors can search.
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');

Here, for these 2 tables, I am having to 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!
Can’t be doing that.
Also, 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!
Can’t be doing that.
You get my point ?

So, 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);

[idrisalisylbd]

Hello again,

Incase anybody is wondering how far I went along to build the search page & pagination, then here is a rough of what I managed:
CONTEXT

<?php
//Report Error.
ini_set('display_errors',1);
ini_set('display_startup_errors',1);
error_reporting(E_ALL);

//Valid $_GET Items.
$table = array('links_crawls','links_submits');

$links_crawls = array('id','date_and_time','domain','url','title','header','meta_keyword','meta_description',); //Table Cols.
$links_submits = array('id','date_and_time','url','header','description','keyword','keyphrase'); //Table Cols.

//Extract $_GETs.
$table = !EMPTY($_POST['table'])?$_POST['table']:(!EMPTY($_GET['table'])?$_GET['table']:'links_crawls');
$search = !EMPTY($_POST['search'])?$_POST['search']:(!EMPTY($_GET['search'])?$_GET['search']:NULL);
$match = !EMPTY($_POST['match'])?$_POST['match']:(!EMPTY($_GET['match'])?$_GET['match']:'fuzzy');

$page = !EMPTY($_GET['pg'])?intval($_GET['pg']):1;
$limit = !EMPTY($_GET['lmt'])?intval($_GET['lmt']):1;
$offset = ($page*$limit)-$limit;

$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
{
	$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";
}

//Query DB.
mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

$conn = mysqli_connect("localhost","root","","buzz"); //mysqli_connect("server","user","password","db");

mysqli_set_charset($conn,'utf8mb4');

if(mysqli_connect_errno())
{
	printf("Mysqli Connection Error: %s",mysqli_connect_error());
}

$stmt = mysqli_stmt_init($conn);

//Do not want to be writing prepared statements for 2 SQLs. One SQL to count results and another SQL to present results.
//And so, doing 2 loops here using the same prepared statement for both SQLs.
for($i=1;$i<3;$i++)
{
	if($i==1)
	{
		$query = $sql_count; //To fetch the result COUNT.
	}
	else
	{
		$query = $sql; //To fetch the RESULT.
	}
	
	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);
		}
	}
	
	if($i==1)
	{
		mysqli_stmt_execute($stmt);
		mysqli_stmt_store_result($stmt); //Necessary to use with mysqli_stmt_num_rows() when SQL query is SELECT.
		
		//Fetch Matching Rows Count.
		//mysqli_stmt_num_rows() has to come after mysqli_stmt_store_result().
		echo 'Total Result: ' .$rows_count = mysqli_stmt_num_rows($stmt); echo '<br><br>';
		mysqli_stmt_free_result($stmt);
	}
	else
	{
		mysqli_stmt_execute($stmt);
		$result = mysqli_stmt_get_result($stmt);
	
		while($row = mysqli_fetch_array($result,MYSQLI_ASSOC))
		{
			$rank = $offset+1;
			if($index=='link_crawls') //Crawled Sitemaps.
			{
				$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>";
			}
		}
		mysqli_stmt_close($stmt);
		mysqli_close($conn);
	}
}

echo __LINE__; echo '<br>';
echo 'Total Pages: ' .$total_pages = ceil($rows_count/$limit); echo '<br><br>';

?>

<div name="pagination_section" id="pagination_section" style="background-color:pink;font-family:verdana;font-size:15px;color:black;text-align:left;">
<?php

//PAGINATION SECTION.
//$page = $_SESSION['page'];
$current_url = $_SERVER['PHP_SELF'].'?'.$_SERVER['QUERY_STRING']; echo '<br><br>';
$previous_page_no = $page-1;

$next_page_no = $page+1; 
$previous_page_no_url = str_replace("P=$page","P=$previous_page_no",$current_url); echo '<br>';
$next_page_no_url = str_replace("P=$page","P=$next_page_no",$current_url); echo '<br>';

$first_page_no_url = str_replace("P=$page","P=1",$current_url); echo '<br>';
$final_page_no_url = str_replace("P=$page","P=$total_pages",$current_url); echo '<br>';

//GO TO: First Page
if($page>1)
{
	?><a href="<?php echo $first_page_no_url;?>"><b><p style="color:black;font-size:25px;"><<</p></b></a>
	<?php
}
echo '<br>';
//GO TO: First Page
if($page>1)
{
	echo "<a href=\"$first_page_no_url\"><b><p style=\"color:black;font-size:25px;\"><<</p></b></a> ";
}
echo '<br><br>';

//GO TO: Previous
if($previous_page_no!==0)
{
	echo '<a href="' .$previous_page_no_url .'"'.'><< Previous</a> ';
}
echo '<br>';

//GO TO: Previous
if($previous_page_no!==0)
{
	echo "<a href=\"$previous_page_no_url\"><< Previous</a> ";
}
echo '<br><br>';	

echo 'Pages: ';

//SIMPLE SEARCH PAGINATION SECTION
$i = 0;
while($i<$total_pages)
{
	$i++;
	//Page Format: $_GET List.
	$array = array("I"=>"$index","C"=>"all","S"=>"$search","M"=>"$match","L"=>"$limit","P"=>intval($i));
	
	$serps_url = $_SERVER['PHP_SELF'].'?'.http_build_query($array,'','&amp;'); //Did convert '&' to '&amp' and so NO need to add htmlspecialchars() when echoing link.
	
	if($i==$page)
	{
		echo '<a href="' .$serps_url .'">' ."<b>$i</b>" .'</a>'; //No need to add htmlspecialchars(), to convert '&' to '&amp;', when echoing link here.
	}
	else
	{
		echo '<a href="' .$serps_url .'">' ."$i" .'</a>'; //No need to add htmlspecialchars(), to convert '&' to '&amp;', when echoing link here.
	}
}
echo '<br><br>';
//GO TO: Next Page
if($next_page_no<$total_pages+1)
{
	echo ' <a href="' .$next_page_no_url .'">Next >></a>';
}
echo '<br>';

//GO TO: Next Page
if($next_page_no<$total_pages+1)
{
	echo " <a href=\"$next_page_no_url\">Next >></a>";
}
echo '<br><br>';

//GO TO: Final Page
if($page<$total_pages)
{
	?><a href="<?php echo $final_page_no_url;?>"><b><p style="color:black;font-size:25px;"> >></p></b></a>
	<?php
}
echo '<br>';
//GO TO: Final Page
if($page<$total_pages)
{
	echo "<a href=\"$final_page_no_url\"><b><p style=\"color:black;font-size:25px;\"> >></p></b></a> ";
}
?>
</div>
<?php

if($_SERVER['REQUEST_METHOD']=='POST')
{
	echo __LINE__; echo '<br>';
	session_destroy();
	session_start();
	echo __LINE__; echo '<br>';
	check_user_inputs();
}

if(ISSET($page) && ISSET($total_pages))
{
	if($page==$total_pages)
	{
		echo __LINE__; echo '<br>'; //DELETE
		unset_sessions();
		//search_form();
	}
}

search_form();
//Script Flow ends here.
?>
</div>


<?php
//NOTE: FUNCTIONS FROM THIS POINT ONWARD.

echo __LINE__; echo '<br>';

function unset_sessions()
{
	session_destroy();
}

function search_form()
{
	?>
	<div style='font-family:verdana;font-size:15px;color:black;text-align:center;' name="search_form" id="search_form" align="center" size="50%">
	<form name='web_search_form' id='web_search_form' method='GET' action="">
	<legend style='font-family:verdana;font-size:15px;color:black;text-align:center;'><b>Web Search</b></legend>
	<fieldset>
	<label for="S" style="font-family:arial;font-size:15px;color:black;text-align:center;"><b>Search</b></label>
	<input type="search" name="S" id="search" size="50" maxlength="255" title="Type a Keyword or Phrase...(Min 1 character & max to 255). For a wild-card or fuzzy match, type * or _ for a single wild-card character match. Else, type % for multiple wild-card character match." <?php if(EMPTY($search)){echo 'placeholder="Word or Phrase..."';}else{if($match == 'fuzzy'){echo 'value='.'"'.str_replace('%','*',$search).'"';}else{echo 'value='.'"'.$search.'"';}}?> style="background-color:white;font-family:verdana;
	font-size:15px;color:black;text-align:center;">
	<input type="radio" name="M" id="exact" value="exact" title="Match: Exact" <?php if(ISSET($match) && $match == 'exact'){ echo 'checked';}?>>
	<label for="exact" style="font-family:verdana;font-size:15px;color:black;text-align:center;">Exact Match</label>
	<input type="radio" name="M" id="fuzzy" value="fuzzy" title="Match: Fuzzy" <?php if(ISSET($match) && $match == 'fuzzy'){ echo 'checked';}?>>
	<label for="fuzzy" style="font-family:verdana;font-size:15px;color:black;text-align:center;">Fuzzy Match</label>
	<br>
	<text style="font-family:verdana;font-size:15px;color:black;text-align:center;"><b>Search in Index:</b></style>
	
	<input type="radio" name="I" id="spidered_web_index" value="spidered_web_index" title="Search in: Crawled Links" <?php if(ISSET($index) && $index == 'crawled_links'){ echo 'checked';}?>>
	<label for="spidered_web_index" style="font-family:verdana;font-size:15px;color:black;text-align:center;">Crawled Links</label>
	
	<input type="radio" name="I" id="submitted_web_index" value="submitted_web_index" title="Search in: Submitted Links" <?php if(ISSET($index) && $index == 'submitted_links'){ echo 'checked';}?>>
	<label for="submitted_web_index" style="font-family:verdana;font-size:15px;color:black;text-align:center;">Submitted Links</label>
	
	<br>
	<label for="L" style="font-family:arial;font-size:15px;color:black;text-align:center;"><b>Limit:</b></label>
	<select name="L" id="limit" title="Select how many results to fetch">
	<option value="1" <?php if(ISSET($limit) && $limit=='1'){echo 'selected';}?>>1</option>
	<option value="10" <?php if(ISSET($limit) && $limit=='10'){echo 'selected';}?>>10</option>
	<option value="1000" <?php if(ISSET($limit) && $limit=='1000'){echo 'selected';}?>>1000</option>
	<option value="10000" <?php if(ISSET($limit) && $limit=='10000'){echo 'selected';}?>>10000</option>
	<option value="100000" <?php if(ISSET($limit) && $limit=='100000'){echo 'selected';}?>>100000</option>
	<option value="1000000" <?php if(ISSET($limit) && $limit=='1000000'){echo 'selected';}?>>1000000</option>
	</select>
	<?php
	//<input type="hidden" name="P" id="P" value="1">
	?>
	<br>
	</fieldset>
	<fieldset>
	<button type="submit" id="web_search_button" title="search the Web">Search!</button>
	</fieldset>
	</form>
	</div>
<?php
}
?>
<br>
<br>
<br>
<div style="background-color:yellow;font-family:verdana;font-size:15px;color:gold;text-align:left;" name="footer pane" id="footer pane" align="center" size="50px" width="33%">
<?php 
echo footer_site();
?>
</div>
</body>
</html>

But like I said in my previous post, I need help to auto generate the prepared statements so I can shorten my script. Or it will get too long. On the above code, I just used 2 tables and look how big the script is. Imagine me adding another 18 tables! It will get 10 times longer! Cannot be having the script get that big!

[idrisalisylbd]

@Drummin

I think you are the man to aid me here.
You done something like this here:

How To Get Sql Present Matching Rows In DESC Order After Adding Total Values Of Many Cols?

Hello Developers, How do I write Sql command for Mysql to add up number values of given columns and rank rows based on total values in ascending order ? If there was only a single “keyword point” (kw1_point) to calculate, then I would have written the SQL like this using prepared statements: $sql = "SELECT * from keywords WHERE kw1 = ? OR kw2 = ? OR kw3 = ? OR kw4 = ? order by kw1_point desc LIMIT 100"; Difficulty popsup when there are more than one column’s values to add up. Example: MySq…

But your code is adv level and I am not a proper intermediate level to extract the required code (for this thread) from that lengthy code in that thread. Infact, your code in that thread will take me probably a month to digest as you have used a lot of functions which I am not familiar with and it will take probably a month to familiarise myself with them.
And so, I was wondering if you could extract the required code bits from that code of your’s and make some amends to make it short as possible and then show me the shortened version here so it aids me auto generate the prepared statements like I want.

Btw, I do not know the oop or object format “->”.
Only know the procedural:

$stmt = mysqli_prepare($link, "INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
mysqli_stmt_bind_param($stmt, 'sssd', $code, $language, $official, $percent);

$code = 'DEU';
$language = 'Bavarian';
$official = "F";
$percent = 11.2;

mysqli_stmt_execute($stmt);

So, I would appreciate it if you show me how to do it in that format.
In the meanwhile I am trying some more but you would be better at it. And so, I will wait for your response.

Thanks!

[idrisalisylbd]

@Drummin

This is the best I managed so far.

<?php

ini_set('display_errors',1);
ini_set('display_startup_errors',1);
error_reporting(E_ALL);
?>

<?php

$tables = array('links_crawls','links_submits');

$links_crawls = array('id','date_and_time','domain','url','title','header','meta_keyword','meta_description',); //Table Cols.
$links_submits = array('id','date_and_time','url','header','description','keyword','keyphrase'); //Table Cols.

//Extract $_GETs.
$table = 'links_crawls';
$search = 'mobile';
$match = 'exact';

$table_columns_number = count(${$table});

for($i=0;$i!==$table_columns_number;$i++)
{
	echo $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!==$table_columns_number;$i++)
	{
		$sql_count .= " OR $col_[$i] = ?";
		$sql .= " OR $col_[$i] = ?";
	}
	$sql .= " OR $col_[$i] = ?";
}
else
{
	$sql_count = "SELECT * from $table WHERE $col_[0] LIKE ?";
	$sql = "SELECT * from $table WHERE $col_[0] LIKE ?";
	for($i=1;$i!==$table_columns_number;$i++)
	{
		$sql_count .= " OR $col_[$i] LIKE ?";
		$sql .= " OR $col_[$i] LIKE ?";
	}
	$sql .= " ORDER BY DESC LIMIT $limit OFFSET $offset";
}

$bindString = str_repeat("s",$table_columns_number);
$placeholders = array_fill(0,$table_columns_number,"?,");

//Query DB.
mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);
$conn = mysqli_connect("localhost","root","","buzz"); //mysqli_connect("server","user","password","db");
mysqli_set_charset($conn,'utf8mb4');

if(mysqli_connect_errno())
{
	printf("Mysqli Connection Error: %s",mysqli_connect_error());
}

//generate prepared Statement.
mysqli_stmt_prepare($stmt,$sql);
mysqli_stmt_bind_param($stmt,"$bindString",$placeholders);
	
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt,$rows_count);
mysqli_stmt_fetch($stmt);

?>

I am sure you can better this and shorten it too.
I do see errors.

[rpkamp]

How is this?

$tables = [
    'links_crawls' => ['id', 'date_and_time', 'domain', 'url', 'title', 'header', 'meta_keyword', 'meta_description'],
    'links_submits' => ['id', 'date_and_time', 'url', 'header', 'description', 'keyword', 'keyphrase'],
];

//Extract $_GETs.
$table = 'links_crawls';
$search = 'mobile';
$match = 'exact';
$limit = 10;
$offset = 0;

$columns = $tables[$table];
$table_columns_number = count($columns);
$comparison = $match === 'exact' ? ' = ?' : ' LIKE ?';

$where = [];
foreach ($columns as $column) {
    $where[] = $column . $comparison;
}

$sql = "SELECT * from $table WHERE " . implode(' OR ', $where);
$sql_count = $sql;
$sql .= " ORDER BY DESC LIMIT $limit OFFSET $offset";

[m_hutley]

@rpkamp

$comparison = $match === 'exact' ? ' = ?' : ' LIKE ?';

$where = [];
foreach ($columns as $column) {
    $where[] = $column . $comparison;
}

$sql = "SELECT * from $table WHERE " . implode(' OR ', $where);

See, i’m lazier than you, rpkamp

$comparison = $match === 'exact' ? ' = ?' : ' LIKE ?';
$sql = "SELECT * from $table WHERE " . implode($comparison.' OR ', $tables[$table]) . $comparison;

[rpkamp]

Sure, that works too

[ahundiak]

@idrisalisylbd Do yourself a big favor and take Doctrine’s Database Abstraction Layer out for a test drive. It’s basically a wrapper over the PHP PDO object but, among other things it supports arrays for prepared statements out of the box. And provides a query builder for the sorts of things you seem to be doing.

Even if you don’t end up using it, it is helpful to see how some of the big boys do things.

[Drummin]

@idrisalisylbd idrisalisylbd As you can see there many “big boys” in this forum who know a lot more than I do… I do what I know and that can always be improved upon… I suppose you are looking for something like this.

These are the changes I made to your sample code.
I added a MySQLi Object-Oriented connection at the top of the page as well as session_start(); so search values can be saved instead of using $_GET as I really can’t stand any $_GET values being passed to a query. I also defined $pagename to be used in the pagination code. More on this in a bit. Other factors I left alone.

<?php  
session_start();
ini_set('display_errors',1);
ini_set('display_startup_errors',1);
error_reporting(E_ALL);	

$conn = new mysqli("localhost", "root","","buzz"); 
mysqli_set_charset($conn,'utf8mb4'); 

$pagename = $_SERVER['PHP_SELF'];

Now these “big dogs” have shown us a pretty cool way of building your query using $comparison but I just went with your $col_ approach though I only used 1 main query and defined how fields are being compared by. Maybe not as cool but it works.

$compareby =($match == "exact" ? "=" : "LIKE");

So moving ahead, we need to build an array of search values, 1 for each field you are querying against so In the section where you are building your $col_ array we can also build an array $searchvalues… There is also the need to add % around your search term when using your “fuzzy” LIKE condition so I defined $fuzzy like so it will be % or blank.

$fuzzy = (!empty($match) && $match == "fuzzy" ? "%" : '');

Note: I define array variables as arrays before adding values to them. This for section now looks like this. Also note by placing % on both sides of the term allows for partial work searching such as og would match with frog. See image above were I add random words for testing…

$col_ = array();
$searchvalues = array();   
$fuzzy = (!empty($match) && $match == "fuzzy" ? "%" : '');
for($i=0;$i!==$table_columns_number;$i++)
{
	$col_[$i] = ${$table}[$i];
	$searchvalues[] = $fuzzy.$search.$fuzzy;
}

We also define the page and offset before the query.

$page = (!empty($_GET['pg']) ? intval($_GET['pg']) : 1);
$offset = ($page*$limit)-$limit;

We now build the query adding the $compareby variable where needed much like you already had. Note I changed your count query to call COUNT(*) as 'cnt' so it will return a single record with the count defined to the field ‘cnt’.

$compareby =($match == "exact" ? "=" : "LIKE");
$sql_count = "SELECT COUNT(*) as 'cnt' from $table WHERE $col_[0] ".$compareby." ?";
$sql = "SELECT * from $table WHERE $col_[0] ".$compareby." ?";
for($i=1;$i!==$table_columns_number;$i++)
{
	$sql_count .= " OR $col_[$i] ".$compareby." ?";
	$sql .= " OR $col_[$i] ".$compareby." ?";
} 
$sql .= " ORDER BY `id` DESC LIMIT ".$limit." OFFSET ".$offset;

These queries can now be prepared and executed like so.

////////////////////////////////////////	

$bindString = str_repeat("s",$table_columns_number);

////////////////////////////////////////
$query_count = $conn->prepare($sql_count);			
$query_count->bind_param($bindString, ...$searchvalues); 		
$query_count->execute();
$result_count = $query_count->get_result();
$row_count = $result_count->fetch_assoc();
$rows_count = $row_count['cnt'];
//echo $rows_count;
////////////////////////////////////////
$query = $conn->prepare($sql);			
$query->bind_param($bindString, ...$searchvalues); 		
$query->execute();
$result = $query->get_result();

You could now use while and print_r() the table rows to see the results (but I’ll use the while statement in the display section).

while($row = $result->fetch_assoc()){
	echo "<pre>";
	print_r($row);	
	echo "</pre>";
}

Your display table can be dynamic based off those field arrays you made. The fields however may not be the best for showing as headings. One option is to build a headings array for all fields in your tables with the field names as KEYs => Displayed Name as the value. I place this right below your $links field arrays so new fields can be added at any time.

$headings = array(
	 'id' => "ID"
	,'date_and_time' => "Date and Time"
	,'domain' => "Domain"
	,'url' => "URL"
	,'title' => "Title"
	,'header' => "Header"
	,'meta_keyword' => "Meta Keyword"
	,'meta_description' => "Meta Description"	
	,'description' => "Description"
	,'keyword' => "Keyword"
	,'keyphrase' => "Key Phrase"
);

You are identifying your field array with (${$table}) so we write an IF condition to check if this is an array and we wrap the display in this condition as our display is built off the selected array.

if(is_array(${$table})):

endif;

Inside this we can build our table using foreach to loop through (${$table}) field and use the $headings array to get the display heading based on the field in the <th> columns.
We can use the variable set by the “count” query $rows_count in another IF condition to tell use if there are results to loop through.much like we did for the headings only this time the field is paired with $row to give us the value for the <td> cells.

if(is_array(${$table})):
	echo '<table class="queuelist">
		<tr>'."\r";	
		foreach((${$table}) as $field):
			echo '<th>'.$headings[$field].'</th>'."\r";
		endforeach;
		echo '</tr>'."\r"; 
		if(!empty($rows_count)): 
			while($row = $result->fetch_assoc()){
				echo '<tr>'."\r";	
					foreach((${$table}) as $field):
						echo '<td>'.$row[$field].'</td>'."\r";
					endforeach;
				echo '</tr>'."\r";	
			}
		endif;	
	echo '</table>'."\r";
endif;

Now lets see about getting your <form> working. There were some changes made like changing the action attribute to post and changing input names for consistency to the code being used and adding the name ‘web_search’ to your submit button.

I am not sure if you realize what limit represents on your form and how applies to your query. This is going to the number of records queried for and shown on a “page”. If it were even possible grab 1000000 records and display them on a page without the server crashing, I don’t know if anyone could or would want to scroll through that many records. A reasonable cutoff might be 100 records per page.

Using foreach() and range() you can biuld your <option>s in a more dynamic way. In Fact for the fun of it I made a version with 3 steps. You see range allows you to set a start and end number plus a step by value. For example; 1 to 9 by 2 will give you 1,3,5,7,9. The next step I did was 10 to 25 by 5 then I did 30 to 100 by 10. These give you more options for viewing.

foreach(range(1,9,2) as $r):
	$selected_records_shown = (isset($_SESSION['limit']) && $_SESSION['limit'] == $r ? ' selected="selected"' : '');
	echo '<option value="'.$r.'"'.$selected_records_shown.'>'.$r.'</option>'."\r";
endforeach;
foreach(range(10,25,5) as $r):
	$selected_records_shown = (isset($_SESSION['limit']) && $_SESSION['limit'] == $r ? ' selected="selected"' : '');
	echo '<option value="'.$r.'"'.$selected_records_shown.'>'.$r.'</option>'."\r";
endforeach;
foreach(range(30,100,10) as $r):
	$selected_records_shown = (isset($_SESSION['limit']) && $_SESSION['limit'] == $r ? ' selected="selected"' : '');
	echo '<option value="'.$r.'"'.$selected_records_shown.'>'.$r.'</option>'."\r";
endforeach;

I am not sure why your form is in a function but to get values back into the function so you can have checked="checked" etc they need to be passed when the function is called and picked up in the same way.

search_form($table,$match,$search,$table,$limit);

and

function search_form($table,$match,$search,$index,$limit)

then those variables are present in the function.

So now we can now process the form and sure you can could set the post value directly to a variable like $table = $_POST['table']; but because we are using $_GET for pagination any information that was set by POST would now be gone as new $_GET instances are presented. So what I did is save the 4 search values to session so they will remain as the page reloads.

We check $_POST['table'] against the $tables array of defined tables and set defaults if not defined correctly before setting to session. These 4 search values are wrapped in the button name IF condition. Note: I also unset($_GET['pg']); so the query is NOT looking for example page 5 when a NEW search might only have 1 page of records.

if($_SERVER['REQUEST_METHOD']=='POST' && isset($_POST['web_search'])): 
	$_SESSION['table'] = (!empty($_POST['table']) && in_array($_POST['table'], $tables) ? $_POST['table'] : 'links_crawls');
	$_SESSION['search'] = (!empty($_POST['search']) ? $_POST['search'] : '');
	$_SESSION['match'] = (!empty($_POST['match']) ? $_POST['match'] : 'fuzzy');
	$_SESSION['limit'] = (!empty($_POST['limit']) ? $_POST['limit'] : 1);
	unset($_GET['pg']);
endif;

I also add a Clear button to the form and when pressed triggers this section of code to unset() those session values so you can start again.

if($_SERVER['REQUEST_METHOD']=='POST' && isset($_POST['clear_search'])): 
	unset($_SESSION['table']);
	unset($_SESSION['search']);
	unset($_SESSION['match']);
	unset($_SESSION['limit']);	 
	unset($_GET['pg']);	
	header("location: ".$pagename);
	exit; 
endif;

Because session value may or may not be set I do not use them directly in the query. I set variables “like” the hard coded ones you had with the session values if available or default values. Now after searching and reloading the page your search is still active allowing for pagination.

$table = (!empty($_SESSION['table']) ? $_SESSION['table'] : 'links_crawls');
$search = (!empty($_SESSION['search']) ? $_SESSION['search'] : '');
$match = (!empty($_SESSION['match']) ? $_SESSION['match'] : 'fuzzy'); //exact fuzzy 
$limit = (!empty($_SESSION['limit']) ? $_SESSION['limit'] : 1);

Those 4 search terms are required for the query so we wrap the query code section with the IF condition checking that these variables are not empty.

if(!empty($table) && !empty($search) && !empty($match) && !empty($limit)):
//Query
endif;

I’ll save pagination code for another chapter of this novel.

[Thallius]

@ahundiak

Even if you don’t end up using it, it is helpful to see how some of the big boys do things.

Haha, real big boys is a good description for putting layer over layer over layer until the performance is so bad that you need a own server park for your private todo list app

I like it more to be a real professional boy, thinking about each library and framework if it is really needed or if I can just write my own method for the one or two functions that I need out of a open source framework.

Just my two cents.

[m_hutley]

Think I just got called fat

[idrisalisylbd]

@drummin
Thanks for your time, effort & code.
I will test it out when I get back home and at my desk.
Currently, I am away from home and reading your response on my mobile at the forum emailed me your respose.

[idrisalisylbd]

@rpkamp

Thanks for the code piece.

[idrisalisylbd]

@ahundiak

Thanks for the suggestion.

[idrisalisylbd]

@m_hutley

Thanks for your further shortened code.
Keep it up as I nowadays trying to shorten things down as much as possible without sacrificing quality. But need to learn the shorter methods first.

[Drummin]

Ha ha

[ahundiak]

Big boys was probably an unfortunate choice of words. I avoided professional partly because I no longer get paid for writing PHP and partly because even amateurs can take advantage of open source libraries. Perhaps lazy would have been a more apt description.

I remember all too well the joys of dynamically building SQL statements. The first few times were a bit interesting. After that it was just drudgery. Especially when it came to trying to maintain them. Once some usable query builders came along I never looked back.

I use the Symfony framework in conjunction with Doctrine’s database libraries. Even for small stuff. Layer over layer layer? Guilty as charged. But if you have not taken any modern frameworks out for a test drive lately then you might be surprised at the performance. I use a $6/month Digital Ocean server instance to run most of my Symfony stuff and they run just fine.

Due largely to inflation I am going to claim that this is my three cents worth.

[idrisalisylbd]

@drummin
@rpkamp
@m_hutley
@ahundiak
@Thallius

Dear programmers,

I am back at my home pc desk. Sorry to keep you waiting 6-7 days.
I am going to go through everyone’s generous codes.
But starting with this one following one first …
I was told, instead of this:

if($match == 'exact')
{
    $sql_count = "SELECT * from $table WHERE $col_[0] = ?";
    $sql = "SELECT * from $table WHERE $col_[0] = ?";
    for($i=1;$i!==$table_columns_number;$i++)
    {
        $sql_count .= " OR $col_[$i] = ?";
        $sql .= " OR $col_[$i] = ?";
    }
    $sql .= " OR $col_[$i] = ?";
}
else
{
    $sql_count = "SELECT * from $table WHERE $col_[0] LIKE ?";
    $sql = "SELECT * from $table WHERE $col_[0] LIKE ?";
    for($i=1;$i!==$table_columns_number;$i++)
    {
        $sql_count .= " OR $col_[$i] LIKE ?";
        $sql .= " OR $col_[$i] LIKE ?";
    }
    $sql .= " ORDER BY DESC LIMIT $limit OFFSET $offset";
}

I should be able to do this:

$char = (($match == 'exact') ? '=' : 'LIKE');

$sql_count = "SELECT * from $table WHERE $col_[0] $char ?";
$sql = "SELECT * from $table WHERE $col_[0] $char ?";
for($i=1;$i!==$table_columns_number;$i++)
{
    $sql_count .= " OR $col_[$i] $char ?";
    $sql .= " OR $col_[$i] = ?";
}

switch ($match)
{
    case 'exact':
        $sql .= " OR $col_[$i] $char ?";
        break;
    default:
        $sql .= " ORDER BY DESC LIMIT $limit OFFSET $offset";
}

I am weak at SWITCH stuff. Keep forgetting it as I do not use it much.
Anyway, while I wait for your feed-backs, let me test rkamp & ahundiak first. Then, can move-on to Drummin’s piece.

Btw, after seeing everybodies codes, anyone can think of an update to even further shorten it ?

Just make sure the code is procedural because I do not understand OOP yet. Sorry!

[idrisalisylbd]

Folks,

I forgot to mention a week back. My original post’s code was showing error. Lol!
Have to mention this as I do not want anyone building upon my faults.

Thanks!

Warning: Undefined array key 8 in C:\wamp64\www\Work\buzz\Templates\test.php on line 36

Warning: Undefined variable $stmt in C:\wamp64\www\Work\buzz\Templates\test.php on line 64

Fatal error: Uncaught TypeError: mysqli_stmt_prepare(): Argument #1 ($statement) must be of type mysqli_stmt, null given in C:\wamp64\www\Work\buzz\Templates\test.php on line 64

TypeError: mysqli_stmt_prepare(): Argument #1 ($statement) must be of type mysqli_stmt, null given in C:\wamp64\www\Work\buzz\Templates\test.php on line 64

[idrisalisylbd]

@rkamp

Ok. I have tested your following short as possible code and get noe rror:

//https://www.sitepoint.com/community/t/how-to-auto-generate-prepared-statements-based-on-array-values/413271/5

$tables = [
    'links_crawls' => ['id', 'date_and_time', 'domain', 'url', 'title', 'header', 'meta_keyword', 'meta_description'],
    'links_submits' => ['id', 'date_and_time', 'url', 'header', 'description', 'keyword', 'keyphrase'],
];

//Extract $_GETs.
$table = 'links_crawls';
$search = 'mobile';
$match = 'exact';
$limit = 10;
$offset = 0;

$columns = $tables[$table];
$table_columns_number = count($columns);
$comparison = $match === 'exact' ? ' = ?' : ' LIKE ?';

$where = [];
foreach ($columns as $column) {
    $where[] = $column . $comparison;
}

$sql = "SELECT * from $table WHERE " . implode(' OR ', $where);
$sql_count = $sql;
echo $sql .= " ORDER BY DESC LIMIT $limit OFFSET $offset";

But, I am not fully on intermediate level of programming yet and not used to shorthand stuffs.
Not bright on programming yet. Still slow to understand things or catchup.
But getting there.
Hence, I have a question on these following 2 lines:

What exactly are you doing on this line ?

$columns = $tables[$table];

Doing a print_r(), I see this:

print_r($columns = $tables[$table]);

Array ( [0] => id [1] => date_and_time [2] => domain [3] => url [4] => title [5] => header [6] => meta_keyword [7] => meta_description )

I am guessing you are extracting all the values of the $tables associative array and dumping it into $columns indexed array. If so, then I either re-learnt or learnt for the first time how to dump values from one type of arrays to another. Most likely re-learnt as I forgotten the method as I usually do not deal with assoc arrays but indexed ones. As for multi ones, I do not go near them! I used to know how to extract the keys aswell but forgotten now.

I thought you create arrays like this:

$where = array();

Guessing you creating an array here:

$where = [];

Yes ? If so, learnt something new if I did not forget it.
You know what. Gonna revise on arrays and these array functions again as I have forgotten a lot on arrays as I do not use them much. Especially, the complicated stuffs.

Thanks