0

say, there are two database tables named

user 

id    name
--   -----
1     Mike
2     Bob
3     Tony
4     Christina
5     James

product

product_id    chooser_id   chooser_name   selection_status
----------    ----------   ------------   -----------------
   1               1             Mike              0
   2               2             Bob               1
   3               3             Tony              0
   4               3             Tony              1
   5               3             Tony              1

here data available in the above product table are inserted by the logged in users of user table based on the following mysql syntax.

mysql_select_db($database_XYZ, $XYZ);
$query_test = "SELECT user.id, user.name, product.product_id, product.chooser_id, product.chooser_name, product.selection_status FROM user, product WHERE user.id = '$_SESSION[userid]' AND user.id = product.chooser_id AND product.selection_status = 1 ";
$test = mysql_query($query_$test, $XYZ) or die(mysql_error());
$row_test  = mysql_fetch_assoc($test );
$totalRows_test  = mysql_num_rows($test );

now if the user Tony wants to check the selection_status (0 & 1 are flagged values) of product table inserted by him in a logged in session depending on the following syntax

if (isset($row_test ["selection_status"]) == "1") {
echo $row_test ["selection_status"];
}

the syntax unusually returns 1, 1 as he is still logged in while checking the data and it fetches all rows which unexpectedly seem to match mysql query from following part of product table :

product_id    chooser_id   chooser_name   selection_status
----------   -----------   -------------  -----------------
   4               3             Tony              1
   5               3             Tony              1

how to call / check only one row of mysql database in a session array using alternative php conditional statement so that it returns the data of only one row in each check or call like?

product_id    chooser_id   chooser_name   selection_status
----------   -----------   -------------  -----------------
   4               3             Tony              1

or

product_id    chooser_id   chooser_name   selection_status
----------   -----------   -------------  -----------------
   5               3             Tony              1

please note that, mysql LIMIT and WHERE product_id=4 etc. won't be applicable here since data shall be populated here through session.

Klanto Aguntuk
  • 719
  • 1
  • 17
  • 44
  • Why are you testing `isset($row_test ["selection_status"])`? `mysql_fetch_assoc()` will always set that. – Barmar Mar 13 '13 at 15:05
  • I don't understand the question. Do you just want to know if there are any rows where `selection_status = 1`? Then just check if `$totalRows_test > 0`? – Barmar Mar 13 '13 at 15:06
  • Why won't it be user specific? The query has `WHERE user.id = '$_SESSION[userid]'`. – Barmar Mar 13 '13 at 15:21
  • Why doesn't `LIMIT 1` do what you want? I don't understand the last line of the question about it not being applicable. – Barmar Mar 13 '13 at 15:24
  • The WHERE clause chooses the data that's selected, and then LIMIT 1 picks 1 row from that result set. – Barmar Mar 13 '13 at 15:31
  • [Please, don't use mysql_* functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) in new code. They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [red box](http://uk.php.net/manual/en/function.mysql-connect.php)? Learn about [_prepared statements_](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide which. – TNK Mar 13 '13 at 15:32
  • the user specific data should be both `product_id` & `selection_status` specific single row data. the syntax shouldn't fetch data from `product_id=4` when data of `product_id=5` are being populated on page. does LIMIT 1 serve that purpose? doesn't LIMIT 1 fetch the first 1 element like, `product_id=4` of the array unless `MYSQL` syntax is defined with `ORDER BY product.product_id DESC`? the possible syntax should filter only one row base on both `product_id` & `selection_status` even there are 1000 of `selection_status` matches there. `product_id` data are identical here. – Klanto Aguntuk Mar 13 '13 at 16:07

1 Answers1

0

the problem has been solved. in if (isset($row_test ["selection_status"]) == "1"){ . isset was returning true or not null over available data. so i removed it and changed the syntax to if ($row_test ["selection_status"] == "1"){ . moreover i erase the condition AND product.selection_status = 1 from mysql syntax.

lesson to learn from this post: flagged value as condition in session based complex mysql query for filtering unique/primary id specific row can't maintain the rule of session variable. this is an exceptional case which doesn't follow session rule. in this case flagged value should be called using php conditional or comparison statement when necessary other than mysql query.

Klanto Aguntuk
  • 719
  • 1
  • 17
  • 44