up vote 1 down vote favorite
share [g+] share [fb]

EDIT: after looking through all the suggestions here and still coming up with no result I changed the database (to a standard osCommerce one) and got a result. Switch back to the downloaded database - no result. Comparing the two showed no structural differences (other than a few extra fields in the live one) Re-exported the live database to my test computer and - voila! Result now shows.

I have no idea what was wrong with the original db - thanks to all for their replies.

Baffled here: I am looking at a bunch of code written in PHP4 but running under PHP5 - the code (abridged) looks like: 

$pid=(int)$customer_exists['pid'];//this value comes from an earlier query & does exist

$query2 = mysql_query("SELECT * FROM `products` AS p, `products_description` AS pd WHERE pd.products_id=p.products_id AND p.products_id='".(int)$pid." '"   );

This gets no result from the database. If the first line is changed to $pid=28; or if the query is changed to just search one table then we get a result.

Echo out $pid before and after the query will echo the value assigned in line 1

(have tried many versions of the actual $query2 - all get the same non-result)

What am I missing here?

Thank you

Graeme

link|improve this question
Why do you cast $pid twice? Moreover if $pid is an int it's not necessary to put it within quotes in your query. Try to echo your query in order to see how the query is resolved by php. – nick rulez Aug 8 '11 at 10:27
feedback

4 Answers

up vote 2 down vote accepted

Can you try changing this 

AND p.products_id='".(int)$pid." '"

to 

AND p.products_id='".(int)$pid."'"

theres a space after double quote and before single quote which you don't need. Also, you don't need (int) here as you already do it in the previous line.

Let me know if it works.

link|improve this answer
and better compare with an integer, the field seems to be an integer here.Lets not loosely use MySQL's loose datatype comparison. AND p.products_id= ".(int)$pid – DhruvPathak Aug 8 '11 at 10:32
Thank you for the input - this made no difference I'm afraid – user883836 Aug 8 '11 at 10:32
@Graeme - could you "echo $query2;" and paste in here? – Wbdvlpr Aug 8 '11 at 10:35
SELECT * FROM products AS p, products_description AS pd WHERE pd.products_id=p.products_id AND p.products_id='3351' is what I am now echoing to the screen - this returns 0 rows although the value 3351 is in both tables – user883836 Aug 8 '11 at 10:47
hmm ... query looks fine to me. Is there any mysql_error()? have you tried executing this query in phpmyadmin? – Wbdvlpr Aug 8 '11 at 10:51
show 1 more comment
feedback
up vote 0 down vote

As stated by Wbdvlpr there is an extra space:

In addition, you don't need to concatenate the string anymore if the statement is enclosed with double qoutes:

$query2 = mysql_query("SELECT * FROM `products` AS p, `products_description` AS pd WHERE pd.products_id=p.products_id AND p.products_id='$pid'");
link|improve this answer
feedback
up vote 0 down vote

Are you sure that (int)$customer_exists['pid'] is 28?

check if the query is right, for example:

$statement = "SELECT * FROM `products` AS p, `products_description` AS pd WHERE pd.products_id=p.products_id AND p.products_id='".(int)$pid." '";
echo($statement);
$query2 = mysql_query($statement);

anyway using (int) in string concatenation does not seem useful

and dont forget to mysql_fetch_assoc but I'm sure this is not the problem if this worked under php4.

link|improve this answer
SELECT * FROM products AS p, products_description AS pd WHERE pd.products_id=p.products_id AND p.products_id='3448' is the sort of result I get from this (int) is not really required - you are correct. Any integer subsituted for $pid before the MySQL query will get a result. Likewise if a single table is used than a result canbe achieved – user883836 Aug 8 '11 at 10:35
feedback
up vote 0 down vote

There are two points to improve here:

  1. mysql_* functions are deprecated of PHP 5.3 and will be removed soon, so don't use them. Use mysqli_* or PDO instead.
  2. This is a perfect code for SQL-injection (and that's bad). Better use prepared statements.

Example for PDO:

$pid=(int)$customer_exists['pid'];
$conn = new PDO($dsn, $user, $pass);
$stmt = $conn->prepare("`SELECT * FROM `products` AS p, `products_description` AS pd WHERE pd.products_id=p.products_id AND p.products_id='?'");
$result = $stmt->query(array($pid)); //note an array here
//Iterating
foreach ($result->fetchAll() as $row){
    //do what you need here
}

See http://www.php.net/manual/en/pdo.construct.php for explanation PDO constructor parameters.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard