TalkPHP - Grabbing IDs from Database

TalkPHP (http://www.talkphp.com/forums.php)

- Absolute Beginners (http://www.talkphp.com/absolute-beginners/)

- - Grabbing IDs from Database (http://www.talkphp.com/absolute-beginners/4572-grabbing-ids-database.html)

9three

06-19-2009 02:48 AM

Grabbing IDs from Database

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 (Post 25646)

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 (Post 25646)

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 (Post 25648)

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.

All times are GMT. The time now is 11:47 AM.