TheStudent2023

@barand Thanks, I getting no errors now! Cheers!

You say, this would work then ? ```` mysqli_stmt_bind_param($stmt,$CharTypes, ...$SearchValues); ````

LINE 96: mysqli_stmt_bind_param($stmt,$CharTypes,$SearchValues);

Php Gurus, 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: 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: $srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch,$srch Then know this that, 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: I get no errors and get echoed the search result: Array ( [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 => mobilephones.com [url_point] => 0 [description] => [description_point] => 0 )

Structure can be corrected later. Generating the SQL based on the array values of the chosen mysql table.

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

@ginerjm I have no real programming background. Did not attend any course in school. Self learning at home from the likes of you in forums and from tutorial sites. That is all.

Fellow Programming Buddies, Howabout we get brainstorming unique features for a new searchengine and each of us can work on our own thoughtup feature and share the codes here for everyone's benefit. You never know, this forum might even adapt to our way. Ofcourse, the codes should have no strings attached. 100% free. Let’s have fun shall we ? Let us think of any some features that will make it more WOW than the current big boys. Yes ? Just imagine, your own way of searching. Your own searchengine. Your own baby. Your own PAL! I am interested! What about you ? Yes, YOU!

@kicken Your turn to thinkup some features that will marvel either the consumer or the listed websites. Take your time (days) to ponder them. have this thread at the back of your subconscious and one morning you might wakeup with a bright idea.

This is what I have in mind: A. As a loyal user of the searchengine, you should earn in some way from your search activities. B. As a listed website, your links should earn in some way, even from activities of: 1. non-buyers; 2. back button hitters; 3. competing links. Now let us revolve our features around these 2 ideas and thinkup new features for a new searchengine.

Brainstormers, Let me make the first move. Then, if you get any ideas, you may chime in. **My SEARCHENGINE FEATURE SUGGESTIONS AS A CONSUMER/SEARCHER/VISITOR/USER** 1. I would like to see the SERPS listing LIKES,LOVES, DISLIKES, HATES of each link listed on the SERP. 2. I prefer to see SERPs listing fone numbers of the websites so we can call the websites. This way, we fo not need to clickover to the website and search for the contactus page to find their phone numbers. (SE can charge websites on a pay per call model). 3. Same as B above. But this time it's the chat page link listed. 4. SERPS should list how many visitors the listed links on the SERPS currently have. (How many visitors the searchengine has currently sent). Now you can clickover to the most busiest link of them all as that tells you it's popular than all the other sites listed on the SERP. 5. After you click links on the SERPS and visit a website, you need to scroll down the page to find to the content (link description) that was mentioned on the SERP. Better for my browser auto scroll me to the appropriate part of the page and highlighted the content that was listed on the SERP. You know what, there are too many things I would want the likes of google to have but I won't bore you anymore unless this thread gets a life of it's own. It's your turn next.

Tough & Rough programmers (hard guys), If you deem nothing is better than a searchengine then let us see if we can brainstorm a unique & better searchengine atleast. What do you say ? 1. What features would you like in a searchengine as a Website (vendor/seller) ? 2. What features would you like in a searchengine as a Searcher (buyer/shopper/consumer) ?

Hard Women & Gentlemen, What features would you like in a searchengine as a: Searcher (buyer/shopper/consumer). Website (vendor/seller).fed-up of the searchengines. Searchengines have not changed since 1998. Have they ? Let's brainstorm a new way of searching from now on so we can kiss good bye to nowadays seaerchengine. Originally, we used to find links though link directories like Yahoo, Dmoz by clicking through categories and subcategories to find the right links. Then alta-vista.com or webcrawler.com built the first searchengine where we started to search for keywords and the SE presented us the right ir wrong links. Are you not fedup finding links this way now ? 25yrs is enough! Let's think of other ways. New ways. Are you not capable of thinking up better ways ? You are programmers! You do not need a business man to come up with a new solution. Now do you ? In short: What Would You Like To See In Tomorrow's 2023 SearchEngine ?

@kicken Thank you very much for the insight! I am reading your latest reply nearly 2wks later! We had storm here and elec cables get ripped on the roadsides and we get current blackout. Sometimes it takes days for all cables on the streets on all local areas to get repaired in full. Anyway, back online now. One other thing. Someone pointed another flaw in my mysql table design as there is no way to findout which keyword matched in the row/record (in the keyword search result) since I got many keywords in the same row and some of these keywords are no matches for my keyword search. In short, SQL won't know which keywords points to count and which ones to ignore since they (non matching keyword & points & matching keywords & points) are all on the same row/record. Anyway, I had a hunch originally, how to achieve this with php. But, I thought SQL will solve it better but it seems I was wrong. Originally, I did not want to do the link ranking after keywords points counts with php as to shorten the code plus it would be slower since php is interpreter and so thought best let the Mysql compiler do the ranking as it will be faster. But now I see Mysql does not even do what I want with my table structure. One table won't do.

@kicken @ginerjm Sorry if I wasted your times with faults in my original post's code.

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*

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

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

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.

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!

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

Thanks. If I get stuck, I'll pester you again. In the meanwhile, got to hassle you some more. Imagine, you want to build a mini searchengine. Let us see the tables and their cols that you yourself will build. Don't bother typing the col names. Instead, if you do not mind, write the SQLs that will build the tables and their cols. And I will feed them to my mysql for them to be built on auto. Plus, I get to see the SQL commands and can learn from them. Most of all, I am curious to see what you name the tables, how many tables you create and what you name the cols and how you link them together and what SQLs you use. Ok. You showed me something above but that was rough, which you thoughtup at the top of your head, based on my question. SO my strings were attached. This time no strings attached. You are free to do things your way. SQL is not my thing. I only know the basics. SELECT, DELETE, UPDATE, INSERT. That is all. Learnt them when learning to build PAGINATIONs. So that CASE thing will give me some hiccups. Checking it thoroughly right now. Had a glance for a min.

@kicken Ok. You spotted right here. More than one link in the same table "phpDOTcom". Ok, then let us change this to UNIQUE where one url per table. Now, even though you deem my table arrangement is UNORTHODOX , I still wish to learn how to achieve my 2 purposes with SQL that I asked you and ginerjm above. Just for my learning purpose, to satisfy my CURIOSITY, do you mind handing me the SQL ? I want to experiment and fiddle. Then, after that, I can move onto experimenting & fiddling your WAY of doing things. This is how I gain experience. Do things UNORTHODOX first and then ORTHODOX. I know it is very easy for you to draw-up the SQL I asked you and I know it would be peasy for you to draw-up the SQL I asked ginerjm. So, how-about drawing them up for me ? Let me play tonight with these 2 SQLs. Then tomorrow, I can move-on to your ORTHODOX way of doing things.

Folks, Reading my two previous posts may confuse you. You may think I am asking the same question or asking for the same SQL here. But I am not. If you notice the result … of both posts and glance at the two final tables on both posts then things will become clear. And so, I asked kicken one question and asked ginerjm a different question. They are similar but not same. You are welcome to chime in and answer my above two posts if you wish.

@ginerjm I have another SQL query similar to the SQL query I asked for on my previous post to kicken. I do not think it will be fair on kicken to work out for me 2 sophisticated SQL queries and so I hope you won't mind me expecting you to show me how this 2nd SQL should be ? You see, here, I am searching for an SQL that will search for records with EXACT MATCHING keyword per column but RANK the records first based on number of matching keywords found per record and then based on score per record. Say, I did an EXACT MATCH (no wildcard) keywords/keyphrase search for: **php book usa** Or, **php+book+usa** Now, it should not matter in what arrangements the searched keywords are in BUT ... 1. how many MATCHING keywords exist in a record. (1st priority). 2. total score of record of matching keywords' points. Aslong as any keyword exists in a record, even one matching keyword, that record should be presented to the searcher. Say my table records look like this: **Table: Links** id | url | kw1 | kw1_point | kw2 | kw2_point | kw3 | kw3_point | kw4 | kw4_point 0 | kickenDOTcom | **usa** | 3 | phone | 3 | apps | 2 | tutorial | 2 1 | mypointDOTcom| uk | 1 | **php** | 4 | apps | 3 | price | 3 2 | borobhaisabDOTcom | tutorial | 3 | **book** | 3 | **php** | 2 | **usa** | 3 3 | phpDOTcom | PHP’s | 5 | books | 5 | united states america | 5 | prices | 5 4 | pythonDOTcom | python | 5 | **book** | 5 | apps | 5 | **usa** | 5 I have highlighted the matching keywords. **NOTE 1:** Notice that the row, **id:3**, actually got the matching keywords. But since the keywords are not EXACT, then SQL should NOT draw-up this record. **PHP’s** is not an exact match of **php**. **Books** is not an exact match for **book**. **united states america** is not an exact match for **usa**. Hence, **record: id:3**, should be **IGNORED**. Also notice record **id:4**. It got **php.com**. But that is not an exact match for **php**. Hence, that Mysql **cell** should be **ignored** too. **NOTE 2:** Record **id: 2** got **3 matches**, while **record id: 4** got **2**. However, record **id:3** scores a total of **10 points** while record **id:2** scores **8**. And so, even though record *id:4* got more points, you can clearly see that, it has less matching keywords than record **id:2**. Hence, in this case, record **1d:2** should get **better ranking** when presented to the keyword searcher. Because here, in this 2nd SQL I seek, ranking should **BE based on how many keywords matched** in a record AND after that, ranking should be based on **total points of all matching keywords** in a record. So, this is how the SQL should rank things before presenting the records to the keyword searcher: **Result ...** 2 | borobhaisabDOTcom | tutorial | 3 | **book** | 3 | **php** | 2 | **usa** | 3 **-------8 point** 4 | pythonDOTcom | python | 5 | **book** | 5 | apps | 5 | **usa** | 5 **-------------------- 10 point** 1 | mypointDOTcom| uk | 1 | **php** | 4 | apps | 3 | price | 3 **---------------------- 4 point** 0 | drumminDOTcom | **usa** | 3 | phone | 3 | apps | 2 | tutorial | 2 **---------------------- 3 point** The **total point** section on the right on the above presented result, is for your convenience ONLY to understand things how I want done. Obviously, I do not expect SQL to echo points in that manner. I can write php to do that, if I really want to. You may have a question, why should SQL present record **id: 0** here, since the record is **totally irrelevant**. **ANSWER:** It does not matter if the record is irrelevant or not to the keyword search in such examples. There is an **EXACT matching** keyword here “**usa**”. And so, this record counts in this EXACT MATCH query. **So, how to write the SQL ?** Thanks for your time & effort.