PHP Help with inherited database site

[johngordon12]

Hope someone can help with this, although I might be a bit vague. (But will be happy to post any code that might help.)

Basically I do a few sites for a company, and everything is coming together nicely moving everything to a new reseller hosting account where everything can site. I'm basically doing some redesign and new database work for them.

But there's one site I'm basically just trying to move over, that already has a database. I've copied the site onto the new hosting, copied the database across and created a new connection file.

I must admit the code is all a bit more PHPO heavy than what I'm used to working with. so have run into a problem trying to figure out what data is not displaying due to queries failing. As far as I can tell the connection is working, and the queries have not changed.

Anyway - the main page is here, where you should be able to click on the furniture images to go to the product page :

http://www.miradatravelmedia.com/lusty/public_html/index.php

But when you click through the product page query is failing :

http://www.miradatravelmedia.com/lusty/public_html/products.php?category=1

The products page is mostly PHP and looks like this :

[PHP]<?php

if (!@empty($_REQUEST["img"])) {

  require_once("../includes/hft_image.php");

  $img = new hft_image($_REQUEST["img"]);

  $img->resize(200,180,"-");

  $img->output_resized("");

  exit;

}

$page = "products";

include("../includes/header.php");

include("../includes/db_open.php");

$sql = "SELECT `id`, `name` FROM `categories` WHERE `id` = '" . $_REQUEST["category"] . "'";

$result = mysql_query($sql) or die("Query failed : $sql at line " . __line__);

$category = mysql_fetch_assoc($result);

?>

<div id="trail"><a href="shop.php">Shop</a> &gt; Lloyd Loom <?php echo $category["name"]?></div>

  <div class="clear" id="divider1"><img src="images/spacer.gif" alt="<?php echo keyword()?>" /></div>

<h1>Lloyd Loom <?php echo $category["name"]?></h1>

<?php

$sql =

  "SELECT * " .

  "FROM `products` " .

  "WHERE `category` = '" . $_REQUEST["category"] . "' " .

  "ORDER BY `order`";

$result = mysql_query($sql) or die("Query failed : $sql at line " . __line__);

if (mysql_num_rows($result) == 0) {

?>

<p>Coming soon ...</p>

<?php

} else {

  $n = 0;

  while ($row = mysql_fetch_assoc($result)) {

    $n++;

?>

<a href="product.php?id=<?php echo $row["id"]?>" class="product"<?php if ($n == mysql_num_rows($result)) echo " id=\"last\""?>>

<?php

    if ($file = glob("images/products/" . $row["id"] . "_*.*")) {

?>

<img src="img.php?img=<?php echo $file[0]?>&width=200&height=180" alt="<?php echo $row["code"]?>" />

<?php

    } else {

?>

<div id="no_image">No image found</div>

<?php

    }

?>

<?php echo $row["code"]?>

</a>

<?php

    if (is_int($n / 4)) {

?>

<div class="clear"><img src="images/spacer.gif" alt="<?php echo keyword()?>" /></div>

<?php

    }

  }

}

?>

  <div class="clear" id="divider2"><img src="images/spacer.gif" alt="<?php echo keyword()?>" /></div>

<?php

include("../includes/footer.php");

?>[/PHP]

If anyone could shed any light on what's going wrong still, that would be much appreciated.

Thanks.

[BenPleysier]

$sql = "SELECT `id`, `name` FROM `categories` WHERE `id` = '" . $_REQUEST["category"] ."'";

If the above has worked int the past, then have a look at the single quotes ` as opposed to '

[johngordon12]

Tried it with ' instead of what was there, and also with no quotes at all, but no joy.

[David_Powers]

Putting unfiltered variables directly in a SQL query is asking for major trouble, as it lays you wide open to SQL injection.

Change your query like this:

$sql = "SELECT id, name FROM categories
        WHERE id = " . mysql_real_escape_string($_GET["category"]);

Also, your code simply displays the SQL query if it fails. Try displaying the error message from MySQL:

$result = mysql_query($sql) or die(mysql_error());

[johngordon12]

Thanks David.

I put the error reporting line in, and got the error message :

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 ''categories' WHERE 'id' = '1'' at line 1

So it does look a bit odd with the quotes.

I'll try your suggested edit - but is there any reason its not working after me moving it, when it is working OK here :

http://www.lloydloomonline.com/products.php?category=1

I still haven't managed to establish where this vat.php file is - is there any explanation for it not being visible on the original hosting server where the site is working? And could it have anything to do with the query not working?

[johngordon12]

OK - changed the query line, and am getting the error : no database selected....

http://www.miradatravelmedia.com/lusty/public_html/products3.php?category=1

[David_Powers · Accepted solution]

Iain71 wrote:

OK - changed the query line, and am getting the error : no database selected....

The answer lies in the database connection script, db_open.php. There should be a call somewhere to mysql_select_db().

[johngordon12]

OK, thanks David.

I did change that to a new one - the standard connection file that DW creates :

<?php

# FileName="Connection_php_mysql.htm"

# Type="MYSQL"

# HTTP="true"

$hostname_conLloydLoom = "localhost";

$database_conLloydLoom = "miradatr_lloydloom";

$username_conLloydLoom = "miradatr";

$password_conLloydLoom = "YAoMiWyZId";

$conLloydLoom = mysql_pconnect($hostname_conLloydLoom, $username_conLloydLoom, $password_conLloydLoom) or trigger_error(mysql_error(),E_USER_ERROR);

?>

Compared to the original, which looked like this :

<?php

mysql_connect("localhost", "lloydloo_lloydlo", ":d)sWP@FknfQ") or die("Could not connect : " . mysql_error());

mysql_query("SET NAMES 'utf8'");

mysql_select_db("lloydloo_data") or die("Could not select database");

?>

So I guess what I need to do is edit the original, with the new connection info so it looks more like this :

<?php

mysql_connect("localhost", "miradatr", "YAoMiWyZId") or die("Could not connect : " . mysql_error());

mysql_query("SET NAMES 'utf8'");

mysql_select_db("miradatr_lloydloom") or die("Could not select database");

?>

[johngordon12]

Thanks David - that was indeed where the problem was.

I still have no idea where this vat.php file is though!

[David_Powers]

Iain71 wrote:

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 ''categories' WHERE 'id' = '1'' at line 1

So it does look a bit odd with the quotes.

Column names in a SQL query must not be in quotes. The MySQL error message tells you that the SQL syntax error is "near" the section it refers to. In other words, the error is just before that part of the query. Normally, you need to display the whole query to spot the error, but it's obvious in this particular case, because there's a quotation mark after categories, indicating that you've put the column name in quotes. Anyway, you've solved that problem by using the version I suggested.

I still haven't managed to establish where this vat.php file is - is there any explanation for it not being visible on the original hosting server where the site is working? And could it have anything to do with the query not working?

No, it has nothing to do with the query not working, and the reason you can't see it on the live site is because there's no error. The error message on your test site tells you exactly where to look for the problem. The file vat.php is included by header.php on line 19. Either vat.php isn't on your server, or the code on line 19 of header.php is pointing to the wrong location.