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

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!

Sorry, I'm not fully understanding your question. It seems like what you are wanting is the PHP switch statement? You can also use a platform like Lucene to perform searches.

@dani

Check this and see if you understand my aim or not:
This is the best I managed so far.
Possibly you can shorten this further ? Yes ?

<?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.

Instead of:

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

you should be able to do:

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

@dani

Sorry for the late reply. Have not been on my pc desk for a week now.
Thanks for reminding me a further shortened way.

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

@dani

Sorry if I wasted your time with faults in my original post's code.

@pritaeas

If you do not mind, then tell me, is this a valid SQL or not ?

SELECT * from links_crawls WHERE id = ? OR date_and_time = ? OR domain = ? OR domain_point = ? OR url = ? OR url_point = ? OR title = ? OR title_point = ? OR header = ? OR header_point = ? OR kw_1 = ? OR kw_1_point = ? OR kw_2 = ? OR kw_2_point = ? OR kw_3 = ? OR kw_3_point = ? OR kw_4 = ? OR kw_4_point = ? OR description = ? OR description_point = ? ORDER BY DESC LIMIT 10 OFFSET 0

If so, then why I get error ?

Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DESC LIMIT 10 OFFSET 0' at line 1 in C:\wamp64\www\Work\buzz\Templates\short.php on line 96

Context:


<?php

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

//Valid tbls & Columns.
$tbls = [
           'links_crawls' =>
                            ['id' => "ID"
                            ,'date_and_time' => "Date and Time"
                            ,'domain' => "Domain"
                            ,'domain_point' => "Domain Point"
                            ,'url' => "URL"
                            ,'url_point' => "Url Point"
                            ,'title' => "Title"
                            ,'title_point' => "Title Point"
                            ,'header' => "Header"
                            ,'header_point' => "Header Point"
                            ,'kw_1' => "Keyword 1"
                            ,'kw_1_point' => "Keyword 1 Point"
                            ,'kw_2' => "Keyword 2"
                            ,'kw_2_point' => "Keyword 2 Point"
                            ,'kw_3' => "Keyword 3"
                            ,'kw_3_point' => "Keyword 3 Point"
                            ,'kw_4' => "Keyword 4"
                            ,'kw_4_point' => "Keyword 4 Point"
                            ,'description' => "Description"    
                            ,'description_point' => "Description Point"
                            ],
            'links_submits' =>
                            ['id' => "ID"
                            ,'date_and_time' => "Date and Time"
                            ,'domain' => "Domain"
                            ,'domain_point' => "Domain Point"
                            ,'url' => "URL"
                            ,'url_point' => "Url Point"
                            ,'title' => "Title"
                            ,'title_point' => "Title Point"
                            ,'header' => "Header"
                            ,'header_point' => "Header Point"
                            ,'kw_1' => "Keyword 1"
                            ,'kw_1_point' => "Keyword 1 Point"
                            ,'kw_2' => "Keyword 2"
                            ,'kw_2_point' => "Keyword 2 Point"
                            ,'kw_3' => "Keyword 3"
                            ,'kw_3_point' => "Keyword 3 Point"
                            ,'kw_4' => "Keyword 4"
                            ,'kw_4_point' => "Keyword 4 Point"
                            ,'description' => "Description"    
                            ,'description_point' => "Description Point"
                            ]
];

//Extract $_GETs.
$tbl = 'links_crawls';
$srch = 'mobile';
$match = 'exact';
$lmt = 10;
$offset = 0;

$display_headings = array_values($tbls[$tbl]);
$cols = array_keys($tbls[$tbl]);
$tbl_cols_no = count($cols); echo '<br>';

$comparators = array();

$comparator = ($match == "exact" ? "=" : "LIKE");
$sql = "SELECT * from $tbl WHERE $cols[0] ".$comparator." ?";

/*
for($i=1;$i!==$tbl_cols_no-1;$i++)
{
    $sql .= " OR $cols[$i] ".$comparator ."?";
    $SearchValues .= "$srch,";
}
$sql .= " OR $cols[$i] = ?";
*/

$sql = "SELECT * from $tbl WHERE ".implode(" $comparator ?"." OR ",$cols)." $comparator ?";
//$sql = "SELECT * from $tbl WHERE ".implode(" $comparator ?" ." OR ",$cols)." $comparator ?";

$sql .= " ORDER BY DESC LIMIT $lmt OFFSET $offset";
echo $sql; //echoes: SELECT * from links_crawls WHERE id = ? OR date_and_time = ? OR domain = ? OR domain_point = ? OR url = ? OR url_point = ? OR title = ? OR title_point = ? OR header = ? OR header_point = ? OR kw_1 = ? OR kw_1_point = ? OR kw_2 = ? OR kw_2_point = ? OR kw_3 = ? OR kw_3_point = ? OR kw_4 = ? OR kw_4_point = ? OR description = ? OR description_point = ? ORDER BY DESC LIMIT 10 OFFSET 0

$CharTypes = str_repeat("s",$tbl_cols_no);
$SearchValues = str_repeat("$srch",$tbl_cols_no);

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');

$stmt = mysqli_stmt_init($conn);
mysqli_stmt_prepare($stmt,$sql);

Final line above is LINE 96 where the error occurs.

@dani

Incase you will be wondering what I am doing above.
Generating the SQL based on the array values of the chosen mysql table.

I’m not sure what OFFSET 0 is. You can just leave that out. If you want to start at an offset of 10 or something, you would do LIMIT 10, 100.

Okay, I stand corrected. Apparently OFFSET is a thing and I never knew it.

ORDER BY [column_name] DESC

$comparator = ($match == "exact" ? "=" : "LIKE");
$sql = "SELECT * FROM $tbl WHERE " . implode(" $comparator ? OR ", $cols) . " $comparator ? ORDER BY date_and_time DESC LIMIT $lmt OFFSET $offset";
commented: Thanks! That silved the issue. I forgot to add the column name after ORDER BY. +4

That won't work if the value of $lmt or $offset is invalid. (Or $tbl, etc. for that matter)

If $lmt or $offset variables give an error, the measure can be taken in this way.

$lmt = isset($_GET['limit']) && $_GET['limit'] > 0 ? min($_GET['limit'], 100) : 10;
$offset = isset($_GET['offset']) && $_GET['offset'] > 0 ? $_GET['offset'] : 0;

or check invalid data

if(!is_int($lmt) || $lmt < 1) {
    $lmt = 100;
}
if(!is_int($offset) || $offset < 0) {
    $offset = 0;
}

@Ujube

Why I get this error:

Fatal error: Uncaught ArgumentCountError: The number of elements in the type definition string must match the number of bind variables in C:\wamp64\www\Work\buzz\Templates\short.php on line 96
( ! ) ArgumentCountError: The number of elements in the type definition string must match the number of bind variables in C:\wamp64\www\Work\buzz\Templates\short.php on line 96
Call Stack
#   Time    Memory  Function    Location
1   0.0022  361808  {main}( )   ...\short.php:0
2   0.0095  440672  mysqli_stmt_bind_param( $statement = class mysqli_stmt { public $affected_rows = NULL; public $insert_id = NULL; public $num_rows = NULL; public $param_count = NULL; public $field_count = NULL; public $errno = NULL; public $error = NULL; public $error_list = NULL; public $sqlstate = NULL; public $id = NULL }, $types = 'ssssssssssssssssssss', $vars = [0 => 'mobile', 1 => 'mobile', 2 => 'mobile', 3 => 'mobile', 4 => 'mobile', 5 => 'mobile', 6 => 'mobile', 7 => 'mobile', 8 => 'mobile', 9 => 'mobile', 10 => 'mobile', 11 => 'mobile', 12 => 'mobile', 13 => 'mobile', 14 => 'mobile', 15 => 'mobile', 16 => 'mobile', 17 => 'mobile', 18 => 'mobile', 19 => 'mobile'] )    ...\short.php:96

Here is my code:

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

//Valid tbls & Columns.
$tbls = [
           'links_crawls_drummin' => 
                            ['id' => "ID"
                            ,'date_and_time' => "Date and Time"
                            ,'domain' => "Domain"
                            ,'domain_point' => "Domain Point"
                            ,'url' => "URL"
                            ,'url_point' => "Url Point"
                            ,'title' => "Title"
                            ,'title_point' => "Title Point"
                            ,'header' => "Header"
                            ,'header_point' => "Header Point"
                            ,'kw_1' => "Keyword 1"
                            ,'kw_1_point' => "Keyword 1 Point"
                            ,'kw_2' => "Keyword 2"
                            ,'kw_2_point' => "Keyword 2 Point"
                            ,'kw_3' => "Keyword 3"
                            ,'kw_3_point' => "Keyword 3 Point"
                            ,'kw_4' => "Keyword 4"
                            ,'kw_4_point' => "Keyword 4 Point"
                            ,'description' => "Description" 
                            ,'description_point' => "Description Point"
                            ],
            'links_submits_drummin' => 
                            ['id' => "ID"
                            ,'date_and_time' => "Date and Time"
                            ,'domain' => "Domain"
                            ,'domain_point' => "Domain Point"
                            ,'url' => "URL"
                            ,'url_point' => "Url Point"
                            ,'title' => "Title"
                            ,'title_point' => "Title Point"
                            ,'header' => "Header"
                            ,'header_point' => "Header Point"
                            ,'kw_1' => "Keyword 1"
                            ,'kw_1_point' => "Keyword 1 Point"
                            ,'kw_2' => "Keyword 2"
                            ,'kw_2_point' => "Keyword 2 Point"
                            ,'kw_3' => "Keyword 3"
                            ,'kw_3_point' => "Keyword 3 Point"
                            ,'kw_4' => "Keyword 4"
                            ,'kw_4_point' => "Keyword 4 Point"
                            ,'description' => "Description" 
                            ,'description_point' => "Description Point"
                            ]
];

//Extract $_GETs.
$tbl = !EMPTY($_POST['tbl'])?$_POST['tbl']:(!EMPTY($_GET['tbl'])?$_GET['tbl']:'links_crawls_drummin');
$lmt = !EMPTY($_POST['lmt'])?$_POST['lmt']:(!EMPTY($_GET['lmt'])?$_GET['lmt']:1);
$match = !EMPTY($_POST['mtch'])?$_POST['mtch']:(!EMPTY($_GET['mtch'])?$_GET['mtch']:'fuzzy');
$srch = !EMPTY($_POST['srch'])?$_POST['srch']:(!EMPTY($_GET['srch'])?$_GET['srch']:'mobile');

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

$display_headings = array_values($tbls[$tbl]);
$cols = array_keys($tbls[$tbl]);
$tbl_cols_no = count($cols); echo '<br>';

$comparators = array();

$comparator = ($match == "exact" ? "=" : "LIKE");
$sql = "SELECT * from $tbl WHERE $cols[0]"." $comparator "."?";
$CharTypes = 's';
$SearchValues[] = $srch;

for($i=1;$i!==$tbl_cols_no;$i++)
{
    $sql .= " OR $cols[$i]"." $comparator "."?";
    $CharTypes .= 's';
    $SearchValues[] = $srch;
}
$sql .= " ORDER BY id DESC LIMIT $lmt OFFSET $offset";

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');

$stmt = mysqli_stmt_init($conn);
mysqli_stmt_prepare($stmt,$sql);
mysqli_stmt_bind_param($stmt,$CharTypes,$SearchValues);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
while($row = mysqli_fetch_array($result,MYSQLI_ASSOC))
{
    echo "<pre>";
    print_r($row);  
    echo "</pre>";;
}

Issue is on the FINAL LINE.
For some reason, php is not accepting $SearchValues.
Note 1:

     $CharTypes = ssssssssssssssssssss.

(20 's').

Note 2:

    $SearchValues = $srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch.

(20 $srch).
Hence, The number of elements in the type definition string does match number of bind variables. Therefore, I should get no errors!

Now, if you say that, I cannot have it like this:

    mysqli_stmt_bind_param($stmt,$CharTypes,$SearchValues);

And must have it like this:

mysqli_stmt_bind_param($stmt,$CharTypes,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch);

Then do not forget,

    $SearchValues = $srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch.

Also, the following works, if I switch it to OOP:

    $query = $conn->prepare($sql);  
$query->bind_param($CharTypes, ...$SearchValues); 
$query->execute();
$result = $query->get_result(); 
while($row = $result->fetch_assoc()){
    echo "<pre>";
    print_r($row);  
    echo "</pre>";
}

NOTE: It is:

    $query->bind_param($CharTypes, ...$SearchValues); //One VAR at the end and not 20.

I get no errors and get echoed the search result:
[code]
(
[id] => 3
[date_and_time] => 2023-04-24 00:14:35
[domain] =>
[kw_1_point] => 5
[kw_2] => tutorial
[kw_2_point] => 5
[kw_3] => apps

[kw_3_point] => 5
[kw_4] => usa
[kw_4_point] => 5
[header] => 0
[kw_1] => mobile
[header_point] => 0
[title] => 0
[title_point] => 0
[domain_point] => 0
[url] => mobilephones.com
[url_point] => 0
[description] => 
[description_point] => 0

)

@dani

The above post should answer your question to what I am trying to do.

Folks,

I switched this:

mysqli_stmt_bind_param($stmt,$CharTypes,$SearchValues);

to this:

mysqli_stmt_bind_param($stmt,$CharTypes,...$SearchValues);

And issue resolved!

@dani

Do close this thread.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.