mysql select on multiple table and multiple or condition

Question

i'm need help about sql query to select multiple table with multiple "or" condition. this is my database schema.

Hotel

• id_hotel
• id_state
• id_city
• hotel_name

State

• id_state
• state_name

City

• id_city
• city_name

thi is the query that i'm have been try, but result nothing.

$sql=mysqli_query($connection,"select * from hotel h, state s, city c where h.id_state=s.id_state and h.id_city=c.id_city and (h.hotel_name='$_GET[data]' or s.state_name='$_GET[data]' or c.city_name='$_GET[data]')");

I was try with that code.. and there is no result. Please help me guys.. tq

Answer 1

There are so many issues with your code that telling you and explaining everything you have to fix would take awhile, so I will list the basic and what you need to do.

1. You're not using any error handler at all, you should always use it while testing / developing, for instance MySQL error, code error and the such, which would have told you some of the issues you currently have.

   You can read this page for more information on How to get useful error messages in php.

2. Continuing, you're using $_GET[data] inside double quotes which makes it loses its boundaries as you have not defined any for it, if you had any error handling it would have been pointed out.

   You could have concatenated it " . $_GET['data'] . " or even used the curly brackets to define its boundaries like so {$_GET['data']}

3. You're injecting GET elements directly in your query without sanitizing it, which is a big mistake and a welcome to SQL injection.

All the above is based in the small portion of code you showed us and I am afraid to see the rest of it.

Here is a sample of how it would look like to use JOINs in your query along with parametized MySQLi.

<?php
// yes we want to see errors
ini_set('error_reporting', E_ALL);

// Your database info
$db_host = 'your database host address';
$db_user = 'your database username';
$db_pass = 'your database user password';
$db_name = 'your database name';

$con = mysqli_connect($db_host, $db_user, $db_pass, $db_name);
if ($con->connect_error)
{
    die('Connect Error (' . mysqli_connect_errno() . ') '. mysqli_connect_error());
}

$sql = "SELECT h.id_hotel,
               h.hotel_name,
               s.id_state,
               s.state_name,
               c.id_city,
               c.city_name
          FROM hotel h
          JOIN state s
            ON h.id_state = s.id_state
          JOIN city c 
            ON h.id_city = c.id_city
         WHERE h.hotel_name = ? OR 
               s.state_name = ? OR 
               c.city_name = ?";
if (!$result = $con->prepare($sql))
{
    die('Query failed: (' . $con->errno . ') ' . $con->error);
}

if (!$result->bind_param('sss', $_GET['data'], $_GET['data'], $_GET['data']))
{
    die('Binding parameters failed: (' . $result->errno . ') ' . $result->error);
}

if (!$result->execute())
{
    die('Execute failed: (' . $result->errno . ') ' . $result->error);
}

$result->bind_result($hotel_id, $hotel_name, $state_id, $state_name, $city_id, $city_name);
while ($result->fetch())
{
    echo $hotel_id, " - ", $hotel_name, " - ", $state_id, " - ", $state_name, " - ", $city_id, " - ", $city_name, "\n";
}
$result->close();
$con->close();

• More about bind_result, click me...
• More about bind_param, click me...