Mysql query array

Question

I have two tables:

1 - hotels[id,name,extras] ( name of hotels with column extras which I've select for each one)

2 - extras[id,name] ( here's the extras of hotel like wifi,tv,swim... )

$name = $_GET['name'];
$hotels_q = mysql_query("SELECT * FROM `hotels` WHERE `name`='$name'") or die (mysql_error());
$hotels_row = mysql_fetch_array($hotels_q);
$id = $hotels_row['id'];    
$extras = explode(",", $hotels_row['extras']);

$ekstras_q = mysql_query("SELECT * FROM `extras` order by id") or die(mysql_error());
            While($ekstras_row = mysql_fetch_array($ekstri_q)){
                $eid = $ekstras_row['id'];
                $ename = $ekstri_row ['name'];
            echo '<ul><li><input type="checkbox" name="extras['.$eid.'][]" value="'.$ename.'"';
            if (in_array($eid, $ekstras)) echo'checked';            
                echo'/>'.$ename.'</li></ul>';

Problem is here extras_q displays all entries with checked ones from table, but I want only to display only checked items!

you need a third table to join hotels to extras with (hotel_id, extra_id). also stop using mysql depreciated API and your open to SQL injection attacks — user557846
– user557846, Commented Feb 29, 2016 at 22:27
mysql_* has been deprecated and is no longer supported. It's best coding practices to switch to mysqli_ or PDO — Michael
– Michael, Commented Feb 29, 2016 at 22:28
@Dagon looks like the extras are in the hotels table as a CSV column :( — Don't Panic
– Don't Panic, Commented Feb 29, 2016 at 22:28

Don't Panic · Accepted Answer · 2016-02-29 23:18:20Z

3

Since you're storing your extra IDs in one column as a comma separated string, I think you should be able to do this by not exploding that value, and then using the CSV string as an IN criteria in your second query.

//...
$extras = $hotels_row['extras']; // don't explode

// Use IN with the $extras CSV here
$ekstras_q = mysql_query("SELECT * FROM `extras` 
                          WHERE id IN ($extras) ORDER BY id") or die(mysql_error());

If you are able to modify your database, you can instead create a many-to-many relationship between hotels and extras by adding a table to join those two items together instead of using a CSV column as you currently are. This can make it easier to write queries to select the related records.

If you add a third table hotel_extras with columns hotel_id and extra_id, you can insert one row for each extra that each hotel has. For example, if the hotel with id 1 has several different extras, its entries in that table would look like this:

table: hotel_extras
_______________________
| hotel_id | extra_id |
=======================
|    1     |     1    |
|    1     |     3    |
|    1     |     4    |
-----------------------

Here is an example using PDO of how you could query data from a setup like that:

$sql = "SELECT e.id, e.name
 FROM hotels h
    INNER JOIN hotel_extras he ON h.id = he.hotel_id
    INNER JOIN extras e ON he.extra_id = e.id
 WHERE h.`name` = ?";

$stmt = $pdo->prepare($sql);
$stmt->bindValue(1, $_GET['name']);
$stmt->execute();

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $eid = $row['id'];
    $ename = $row['name'];
    '<li><input type="checkbox" name="extras['.$eid.'][]" value="'.$ename.'" checked/>'.$ename.'</li>';
}

edited Feb 29, 2016 at 23:18

answered Feb 29, 2016 at 22:39

Don't Panic

41.9k11 gold badges70 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

1mnumb1 Over a year ago

I've got Warning: in_array() expects parameter 2 to be array, string given in this line - if (in_array($eid, $ekstras)) echo'checked';

Don't Panic Over a year ago

Oh, if you do it this way, you shouldn't need that line. Since you're only getting the ones from the database that are associated with that hotel, they'll all be checked anyway, so you can just include the checked in your HTML every time.

1mnumb1 Over a year ago

Yes works! But if I make with third table to join others, how need to looks query.

user557846 Over a year ago

in the long run a third table is a MUCH better idea

1mnumb1 Over a year ago

Ok how to explode each one with comma. If I echo "$ename, "; at the end keeps comma.If use explode in $ename - got array to string convertion?

|

Collectives™ on Stack Overflow

Mysql query array

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related