Grabbing IDs from Database

9three · 06-19-2009, 02:48 AM

Hey

I'm creating a simple shopping cart but I'm stuck trying to figure out why I'm not getting the output I'm looking for.

PHP Code:


			
function product_exists($input_id)

{

  $mysqli = new mysqli('localhost', 'root', '', 'store');

  $result = $mysqli->query("SELECT ID FROM products");

  

  $array = array();

  while ($row = $result->fetch_object())

  {

    $array[] = $row->ID;

  }

  

  foreach ($array as $product_id)

  {

    if ($product_id == $input_id)

      return true;

    else

      return false;

  }

}

Basically what I'm trying to do is store all the IDs that are in my database into an array and compare the ID the user has given me. If it matches it needs to return true, else false.

Here is the front side:

PHP Code:


			
$product_id = $_REQUEST['id'];

if (product_exists($product_id))

  echo 'Product Exist!';

else

  echo 'Not working yet';

If I do add.php?id=1 it works fine. But if I do add.php?id=2 it will not work.

Can anyone lend a hand please?

Village Idiot · 06-19-2009, 02:52 AM

First off you are not doing this correctly, you should never leave this sort of processing to the script. Make your query

sql Code:

SELECT id FROM products WHERE id='$product_id'

Just be sure to change
$product_id = $_REQUEST['id']; to
$product_id = (int)$_REQUEST['id'];
to prevent SQL injection.

If it returns a row it exists, if there are no rows it does not.

I would strongly suggest putting anything as complicated as a shopping cart aside to learn SQL.

9three · 06-19-2009, 03:04 AM

Got it. Thank you, I've added the (int).

PHP Code:


			
$result = $mysqli->query("SELECT ID FROM products WHERE ID = '$input_id'");

I'm trying to re factor the loops because if I (for example)4,000 items then the function would need to place all those IDs into an array and then sort through it. I would imagine it would slow down the application a lot.

Village Idiot · 06-19-2009, 03:11 AM

Quote:

Originally Posted by 9three

Got it. Thank you, I've added the (int).

PHP Code:


			
$result = $mysqli->query("SELECT ID FROM products WHERE ID = '$input_id'");

Just a style tip, don't capitalize field names. Generally the only thing that should be caps are keywords. It does not really matter for small stuff like this, but it pays off when you have really big queries.

Quote:

Originally Posted by 9three

I'm trying to re factor the loops because if I (for example)4,000 items then the function would need to place all those IDs into an array and then sort through it. I would imagine it would slow down the application a lot.

Doing it in the script would slow it immensely. SQL can sort though tens of thousands of rows in under a second, doing it in PHP would be slow and memory intensive. A database that returns too much or too little is not doing its job, a good query should return exactly what you need to complete the process at hand. Let SQL do as much of the data processing as possible.

9three · 06-19-2009, 03:21 AM

So this is what I thought of but its returning false on anything bigger than 1

PHP Code:


			
$mysqli = new mysqli('localhost', 'root', '', 'store');
$result = $mysqli->query("SELECT ID FROM products WHERE ID = '$input_id'");
  
  if ($result == $input_id)
    return true;
  return false;

I'm not sure if that's what you meant by allowing SQL do the work?

Village Idiot · 06-19-2009, 03:22 AM

Quote:

Originally Posted by 9three

So this is what I thought of but its returning false on anything bigger than 1

PHP Code:


			
$mysqli = new mysqli('localhost', 'root', '', 'store');
$result = $mysqli->query("SELECT ID FROM products WHERE ID = '$input_id'");
  
  if ($result == $input_id)
    return true;
  return false;

I'm not sure if that's what you meant by allowing SQL do the work?

SQL is doing all the work you need to, just count the rows (mysql_num_rows). This is not incredibly efficient, but better ways would require more advanced SQL. Once again, I highly seggust you learn to code SQL before making a data driven script.