Jump to content

Possible To Auto Generate Prepared Statements ?

TheStudent2023

By TheStudent2023
in PHP Coding Help

 Share

Recommended Posts

TheStudent2023 Advanced Member

TheStudent2023

Posted
Posted (edited)

Hi,

Is it possible to auto generate prepared statements based on table column names and numbers ?

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

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:

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

Edited by TheStudent2023
TheStudent2023 Advanced Member

TheStudent2023

Posted
  • Author
Posted

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!

ginerjm Prolific Member

ginerjm

Posted
Posted

What about writing the query statements?  You're not worried about what the user chooses to inquire about, only how to handle the prepare function?

BTW - box #1 in your post refers to 'these two tables' but all I see is an array definition (improperly written too) but no indication of different sources.

And box #2 shows you doing a prepare using the same value for every column in a supposed query.  How is that useful unless you are still working with a poorly designed database that has multiple identical attributes in a table?

TheStudent2023 Advanced Member

TheStudent2023

Posted
  • Author
Posted
57 minutes ago, ginerjm said:

What about writing the query statements?  You're not worried about what the user chooses to inquire about, only how to handle the prepare function?

BTW - box #1 in your post refers to 'these two tables' but all I see is an array definition (improperly written too) but no indication of different sources.

And box #2 shows you doing a prepare using the same value for every column in a supposed query.  How is that useful unless you are still working with a poorly designed database that has multiple identical attributes in a table?

Check the 2 arrays again. One has 7 values (table cols) and the other has 8. Plus, the values are not all exactly same.Right now, I just need the code to auto write the prepared functions based on the chosen table's array values.

TheStudent2023 Advanced Member

TheStudent2023

Posted
  • Author
Posted

@kicken

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.

ginerjm Prolific Member

ginerjm

Posted
Posted (edited)

You posted 'Imagine these 2 array' which was followed by a block of code that was nothing more than an array.

Hence my confusion.  About this and about what you are trying to do.

IMO - by the time you write something that would accomplish what you seem to be describing you could actually write the code you are trying to avoid.

Edited by ginerjm
TheStudent2023 Advanced Member

TheStudent2023

Posted
  • Author
Posted (edited)

@ginerjm

Sorry for the late reply. Have not been on my pc desk for a week now.

I gave this code: 

	<?php
	ini_set('display_errors',1);
ini_set('display_startup_errors',1);
error_reporting(E_ALL);
	 
	$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 was told to further shorten 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"; }

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

 

Now, can you shorten it any further or not ?

 

Edited by TheStudent2023
TheStudent2023 Advanced Member

TheStudent2023

Posted
  • Author
Posted

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*

TheStudent2023 Advanced Member

TheStudent2023

Posted
  • Author
Posted (edited)

@kicken

 

I have nearly finished my project. I just stuck on one place.

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.

Edited by TheStudent2023
TheStudent2023 Advanced Member

TheStudent2023

Posted
  • Author
Posted (edited)
4 minutes ago, ginerjm said:

Don't have a clue what you are doing and I see you haven't corrected your structure at all.

Structure can be corrected later.

Generating the SQL based on the array values of the chosen mysql table.

Edited by TheStudent2023
Guest
This topic is now closed to further replies.
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.