Any MySQL experts here?

Something that's been bugging me and I can't seem to find the answer, maybe I'm just asking the wrong questions.

Let's say I've got a database of customers, when I currently retrieve a customer with:


$query = "SELECT * FROM contacts WHERE email = ".$customer_email."";
$result = mysql_query($query);
$check_values = mysql_fetch_array($result);
if ($check_values['serialnumber']){
$serialnumber_in_db = TRUE;
}

There's currently no issue because customers are only in the database once.

What if I started multiple entries for customers? If a customer uses the same email address for all transactions, in what form is the data returned in the code above?

Does 'mysql_fetch_array' still work on '$result'? If so, what does the array look like? Would it be of variable length if there are empty fields in the customers row? Or is something else required?

it's hard to answer because I need more info on what it is more specifically you're trying to retrieve.

The statements are incorrect firstly. The: $result = mysql_query($query); is missing the reference to the connection to the database you created. Not sure if this was intended. (needs to be the second option, ie:$result = mysql_query($query,$yourConnection);

Then, the array that is returned, is like most other arrays. it will be like a multidimensional array, the first being each entry, and nested is the array of all your fields. that length of the 2 dimension array that contains all your entries fields will always be how many fields you have in the table. It depends on how you set up you database for what happens in empty fields, but it'll likely be either "" or NULL if there is no value, but each row in the array will be the same length.

You need a while loop to go through the fetch array, like this:
while ($row = mysql_fetch_array($query)){
//do stuff with each row here
}


HTH.

Awesome! Thanks! Yeah I left out the db connection stuff on purpose for this example, that's the line above this. The code works fine as it is.

Much appreciated. This saves me a whole bunch of time!

great.

the only concern possibly is if this is a really busy site, focusing on optimizing it with less it to the database would be a good thing, like not having to comb through the entire database unnecessarily etc.

A couple of comments:

1) If that is a public facing page and/or the $customer_email is passed via the web (form, URL, whatever), you should probably make sure to "escape" the sql ... meaning make sure no one can take advantage of it to get info they are not supposed to. Here's a good URL with a bit of info on how that can be done and what the risk is: PHP mysql_real_escape_string() Function (http://www.w3schools.com/php/func_mysql_real_escape_string.asp)

2) There are also potentially a couple of ways to improve the efficiency of the main statement. Most times, unless you absolutely need ALL of the fields in that table doing a SELECT * is doing more than you need it to do. You could instead do something like

SELECT email, serialnumber, whatever FROM contacts (...)

... might seem trivial but depending on what you're pulling out it can end up making a big difference in memory footprint, etc.

Also, if you want to quickly pull up a list of contacts with or without a serialnumber set you could do something like
SELECT email, whatever FROM contacts WHERE email='".$customer_email."' AND serialnumber = ''

MySQL is much much more efficient (time and memory wise) than PHP, meaning you're best off to narrow the results and only retrieve exactly what you need at the MySQL end of things, as opposed to grabbing really big data sets and then looping through them or processing them in PHP.

Of course these are not necessary, but they are more a "best practice" kind of approach, and if you're just learning it's always best to learn and do things in the "best practice" method instead of learning bad habits right off the top.

The other stuff GT said is also all valid. HTH. If you want more specific advice we need to know more specifics :)

Thanks for the feedback! I'm not just learning per se, just not 100% on some of the functions... Sometimes the docs and forums are just missing the kind of example that makes it make sense, ya know? :)

A couple of comments:

1) If that is a public facing page and/or the $customer_email is passed via the web (form, URL, whatever), you should probably make sure to "escape" the sql ... meaning make sure no one can take advantage of it to get info they are not supposed to. Here's a good URL with a bit of info on how that can be done and what the risk is: PHP mysql_real_escape_string() Function (http://www.w3schools.com/php/func_mysql_real_escape_string.asp)


Glad you are looking out for my best interests. Rest assured, it's obfuscated at the user end, escaped, decrypted, escaped again, checked for validity, encrypted in transit, and then re-verified... I'm a paranoid person. In fact that's why I only posted the bare minimum code...


2) There are also potentially a couple of ways to improve the efficiency of the main statement. Most times, unless you absolutely need ALL of the fields in that table doing a SELECT * is doing more than you need it to do. You could instead do something like
SELECT email, serialnumber, whatever FROM contacts (...)


Also good advice, but I'm already doing that too. :) I just put the * version in there so people couldn't see my variable names... Edit: actually looking back remembering that I stuck the * in there because I wanted to know if empty fields were returned, or if the array would be condensed if not all fields had values.


MySQL is much much more efficient (time and memory wise) than PHP, meaning you're best off to narrow the results and only retrieve exactly what you need at the MySQL end of things, as opposed to grabbing really big data sets and then looping through them or processing them in PHP.


Good to know, shouldn't be a problem for the scale of what I'm doing but I'll keep it in mind, particularly for future projects.


Of course these are not necessary, but they are more a "best practice" kind of approach, and if you're just learning it's always best to learn and do things in the "best practice" method instead of learning bad habits right off the top.


Agreed! And thanks for taking the time! I do appreciate your response, both of you.

yep, I have a php function I run everything through if it's user entered to prevent sql injections.

and +1 on doing the narrowing down in mySQL as php can be faster.