echoing "no search results" even though there is data in database

Question

I am trying to get a search working, I have echoed and used var dump and what I am getting in my browser is:

0 there was no search results

object(mysqli_result)#2 (5) { ["current_field"]=> int(0) 
                              ["field_count"]=> int(4) 
                              ["lengths"]=> NULL 
                              ["num_rows"]=> int(0) 
                              ["type"]=> int(0) 
                            }

But I have typed in Paris which is in my database, so it shouldn't echo there was no search.

Below is my code. Not sure where I am going wrong, any help would be appreciative.

require_once('config1.php');
error_reporting(E_ALL);

$output = '';
if(isset($_POST['search'])){
$searchq = $_POST['search'];

$sql = "SELECT a.attraction_name, a.lat, a.long, a.cost FROM zz_attractions a 
INNER JOIN zz_city c ON a.city_id = c.city_id WHERE c.city_name LIKE 
'%searchq%'";

$result = mysqli_query($conn, $sql);
$count = mysqli_num_rows($result);

if ($count == 0) { 
    $output = 'there was no search results';

    echo $count;
    echo $output;
    var_dump($result);
} else {
    while ($row = mysql_fetch_array($sql)) { 
    $attraction_name = $row['attraction_name'];
    $lat = $row['lat'];
    $long = $row['long'];
    $cost = $row['cost'];

    $output .= '<div>'.$attraction_name.' '.$lat.' '.$long.' '.$cost.'</div>';

    }
}
}


city_id  attraction_id  attraction_name  lat  long  cost
1           1             Eiffel Tower   49    2     25

execute same query in database and check getting any result or not — K.B, Dec 15 '17 at 11:02
'searchq' is not going to be magically replaced with the variable $searchq. And check for sql injection. — user5542121, Dec 15 '17 at 11:05
@KMS I added the data from my attractions table. In the city table there is city_id and city_name which is 1 and Paris — Kl12, Dec 15 '17 at 11:05
_I am using phpmyadmin for my database..._ That was easy to guess although we have 2017... however your question has nothing todo with phpmyadmin — B001ᛦ, Dec 15 '17 at 11:05
`phpMyAdmin` is a tool written in PHP. **It is not a database engine** MySQL is the database engine — RiggsFolly, Dec 15 '17 at 11:07
You're not binding $searchq data to your query and getting nothing — Generwp, Dec 15 '17 at 11:07
How do I replace it? It is an internal uni project and we were told that doesn't matter because it is going to be deleted in a month — Kl12, Dec 15 '17 at 11:07
@RiggsFolly that was a repost and I added it to the duplicate you closed it with. They also had another of the same post not long ago, and it was deleted. Not to mention another of their question. The answer below didn't fully answer the problem and has been an ongoing thing for both the deleted post. — Funk Forty Niner, Dec 15 '17 at 12:35

gnusey · Accepted Answer · 2017-12-15T11:14:14.910

2

Your're missing the $ off the variable that you're passing into your SQL statement. Try

$sql = "SELECT a.attraction_name, a.lat, a.long, a.cost FROM zz_attractions a 
INNER JOIN zz_city c ON a.city_id = c.city_id WHERE c.city_name LIKE 
'%$searchq%'";

Also, have a look at binding parameters to sql statements rather than passing them in directly as your current code leaves you vulnerable to injection https://www.w3schools.com/sql/sql_injection.asp | http://php.net/manual/en/mysqli-stmt.bind-param.php

edited Dec 15 '17 at 11:14

answered Dec 15 '17 at 11:07

gnusey

354
3
16

2

That solution is bad, you are proposing something which allows for SQL injection. Even though, its correct - most likely that is what the OP wanted. – user5542121 Dec 15 '17 at 11:08
1

^^ I left a link to php.net on the topic of binding parameters rather than passing them in directly, for exactly that reason :) – gnusey Dec 15 '17 at 11:10
I am getting this error now Call to undefined function mysql_fetch_array() in URL 21 Stack trace: #0 {main} thrown in URL on line 21 – Kl12 Dec 15 '17 at 11:12
use `mysqli_fetch_array()` – gnusey Dec 15 '17 at 11:14
Now I am getting expects parameter 1 to be mysqli_result. Not sure what this means – Kl12 Dec 15 '17 at 11:19
you're passing your sql statement into `mysqli_fetch_array()` which is incorrect. Pass in your $result variable instead – gnusey Dec 15 '17 at 11:29
Got everything sorted, thanks :) – Kl12 Dec 15 '17 at 12:27
I question the OP accepting this answer. It does not fully solve the question. – Funk Forty Niner Dec 15 '17 at 12:36

echoing "no search results" even though there is data in database

1 Answers1