1

Struggling with this one.. my sql query works in PHP myAdmin but not on the page..

The url: http://www.copleycaravanservices.co.uk/Caravans.php?room_id=1&foreign_id=1 There is also another query which works for the room_id section of the url

The code that isnt working:

<?php
require_once('db_config.php');
mysql_select_db($dbname, $db);
$sql1 = "SELECT * FROM  `SS_hotel_booking_multi_lang` 
WHERE  `foreign_id` = " . $_GET['foreign_id'] . "
AND  `locale` = '1'
AND  `field` =  'description'
AND  `source` =  'data'";
$result1 = mysql_query($sql1, $db) or die(mysql_error());
$CaravanDesc = mysql_fetch_assoc($result1);
?>

The database: (Hope this displays correctly!)

id  foreign_id  model   locale  field   content              source
7365    1       pjRoom    1     name    Promenade 654          data
7366    1       pjRoom    1  description This caravan sleeps 6 data
7371    2       pjRoom    1     name    Butlins Sandhills 14   data
7372    2       pjRoom    1  description 4 Day Passes included data
Andrew Anderson
  • 25
  • 5
  • Use single quotes around your string values: `$sql1 = "SELECT * FROM \`SS_hotel_booking_multi_lang\` WHERE (\`foreign_id\` = " . $_GET['foreign_id'] . " AND \`locale\` = "1" AND \`field\` = 'description' AND \`model\` = 'pjRoom')";` – Mark Baker Mar 22 '15 at 13:33
  • Then consider the risk of SQL injection for `$_GET['foreign_id']` – Mark Baker Mar 22 '15 at 13:33
  • 1
    Then start learning to use MySQLi or PDO with prepared statements/bind variables, rather than the old, deprecated MySQL interface – Mark Baker Mar 22 '15 at 13:34
  • The `or die(mysql_error())` should have thrown you syntax errors. Learn to interpret them and correct them accordingly. @MarkBaker told you what to use. Edit: Correction, it did `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 'model` = 'pjRoom' AND `locale` = '1' AND `source` = 'data')' at line 2` – Funk Forty Niner Mar 22 '15 at 13:36
  • Im using 123-reg as a host and using their standard MySQL interface. – Andrew Anderson Mar 22 '15 at 13:36
  • Thanks mark and fred, the error i get is 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 'model` = 'pjRoom' AND `locale` = '1' AND `source` = 'data')' at line 2 – Andrew Anderson Mar 22 '15 at 13:37
  • Your edit doesn't support the error you're getting: use near 'model` = having a quote and a tick. Update your question and code to reflect this. You can try removing the `()` around your `where` clause. – Funk Forty Niner Mar 22 '15 at 13:50
  • Im really a newbie at all this, im not sure what it means exactly.. What should i do exactly? – Andrew Anderson Mar 22 '15 at 13:55
  • `WHERE (` <= remove that `(` and `= 'data')";` remove that `)`. Update your question with the actual column names and add the error you are getting. – Funk Forty Niner Mar 22 '15 at 13:56
  • `$_GET['foreign_id']` what is going through for that? – Funk Forty Niner Mar 22 '15 at 13:58
  • It should be the foreign_id in the url, so all items in the database where foreign_id=1 for example are pulled up. so in this query i should have two results (rows where ID: 7365 and 7366) – Andrew Anderson Mar 22 '15 at 14:04
  • `room_id` in your URL `?room_id=1` where is that assigned in your GET? I also reopened the question. Plus, what are your column types? – Funk Forty Niner Mar 22 '15 at 14:04
  • the room_id is in another query, it pulls the room_id (resulting in the room name being displayed) from another table all together. – Andrew Anderson Mar 22 '15 at 14:08
  • – Andrew Anderson Mar 22 '15 at 14:09

1 Answers1

0

I think this should be done like this try by removing quotes of column name

 <?php
    require_once('db_config.php');
    mysql_select_db($dbname, $db);
    $sql1 = "SELECT * FROM  `SS_hotel_booking_multi_lang` 
    WHERE  foreign_id = " . $_GET['foreign_id'] . "
    AND  locale = '1'
    AND  field =  'description'
    AND  source =  'data'";
    $result1 = mysql_query($sql1, $db) or die(mysql_error());
    $CaravanDesc = mysql_fetch_assoc($result1);
    ?>
siddhesh
  • 598
  • 6
  • 19