Best way to grab all info from one mysql row?

[dannyb785]

Hi all,

 

Since I first started coding php, if I was ever grabbing one specific row(usually based on the primary key so it was unique) I've always just done:

 

 

extract(mysql_fetch_assoc(mysql_query(" SELECT * FROM User WHERE user_id='1'  ")));

 

But I recently thought to myself, is that the wrong way to do it? Is there a problem with doing that? Or is there a more efficient way to grab all of the data from one row?

[Jessica]

There are a ton of things wrong with that.

You don't check for MySQL errors, you're quoting an integer, and the use of extract hints you might not understand how to manipulate arrays.

[trq]

Agreed. That is some terrible coding right there.

[Sanjib Sinha]

There is a more efficient way. Try to keep your code loosely coupled and catch errors so that your code will be faultless.

[dannyb785]

I don't think my original question was clear. I wasn't asking if the code was best for validation or anything like that. My question was concerning its efficiency. Let's pretend that you knew for a fact that the query would generate the one row for the id given, and that there were no errors, is this the most efficient way to do that? Or is there a better way?

 

I know that I don't really have to extract(), so I'll just reword the line in question:

mysql_fetch_assoc(mysql_query(" ANY QUERY THAT WILL ONLY GENERATE ONE ROW  "));

In terms of using up server resources, is there a more efficient way to generate the result than that?

 

 

Agreed. That is some terrible coding right there.

 

 

Can you kindly give an example of a line of code that would be less "terrible" ?

Edited April 16, 2013 by dannyb785

[DavidAM]

Let's pretend that you knew for a fact that the query would generate the one row for the id given, and that there were no errors

There is no way you can know that for a fact. The database server may crash, which will definitely produce an error. The database may get corrupted (or hacked) and that row may be lost, which will produce zero rows. The connection to the database server may get severed and you would get an error.

 

You cannot assume that code is always going to work just because it should. If you write your code that way, you get absolutely no help when a problem occurs. You have to start rewriting code to get it to show you errors before you can even start trying to fix the problem.

 

This sounds like a case of micro-optimization. The resources saved, if any, by combining those function calls is not worth the additional effort that will be required when you have to debug or modify it.

[dannyb785]

Why won't anyone just say yes or no that the code I provided is efficient or could be made more efficient?

 

Does it use up too many resources to use extract()? Should I avoid using it?

 

I'm not asking for real-world coding. I have my own reasons for being certain that the data in the query won't produce a result. All I'm asking is the quickest(and least resource-heavy) way to grab data from mysql and store it into variables.

[Jessica]

No it's not efficient.

[DavidAM]

Why won't anyone just say yes or no that the code I provided is efficient or could be made more efficient?

Because most of us are professionals who have been there and done that; and suffered the consequences of taking shortcuts. You are getting professional grade advice for FREE. You should at least be grateful that we are providing it. And because there is no one-word answer to the question -- there are too many unknowns when looking at a single line of code.

 

 

Does it use up too many resources to use extract()? Should I avoid using it?

The extract() function blindly creates variables in the current namespace. It will overwrite any variables that already exist with the same name as any selected column; it will fail or error in some way if the column name or alias does not constitute a valid variable name; if SELECT * is used, you get a bunch of variables that you might not need; if the table structure changes (and SELECT * is used), it could overwrite a variable that was used because it was not a column name when the code was written, causing the code to start failing without any changes to the code. Using SELECT * is generally not considered good practice because it wastes resources retrieving, returning and storing (in memory) data that is not going to be used; besides, looking at the code, you can't tell what values are coming back so you don't know what data you can access. If you must use extract you should use the third parameter to provide a prefix for the variable names so you know where they came from when reading the code and it will not overwrite existing variables that are not from the database (as long as you don't use the prefix on non-extracted variables). This still does not address the potential issue of invalid column/alias names.

 

I'm not asking for real-world coding. I have my own reasons for being certain that the data in the query won't produce a result. All I'm asking is the quickest(and least resource-heavy) way to grab data from mysql and store it into variables.

If this is not "real-world" programming, then what's the point. If it takes two nanoseconds longer to check for errors in a script that is not being used on a site that gets a million hits per second, what possible difference could it make. I have written thousands, probably millions of lines of code, no matter how sure I am that I will never modify "this" code in the future, I inevitably have to review it at some point in time. Shortcuts like this not only make the code harder to debug, but make it harder to see what the code is supposed to be doing. Even if I'm not debugging code, I often refer back to code to remember how I did something.

 

The only real way to answer your question is to benchmark it. But, as I said, there are a large number of variables: what else is running on the server; what else is accessing the database; what are you doing with the data that is retrieved -- specifically, how is it being accessed; how much other memory is the script using for other variables; how many instances of this script are running concurrently; and on and on.

[dannyb785]

No it's not efficient.

 

Jessica, your signature says that instead of giving answers to questions,you give hints. But just saying "it's not efficient" isn't helpful in the slightest.

[dannyb785]

I also didn't realize this forum was so full of pompous, arrogant coders who feel like my questions aren't worth their time.

 

I didn't think I'd have to give an example but here you go:

 

A user logs in and I store their user id into a session variable. Then, any time they're on a page that requires their own information(like an 'edit my account' page or something), I run a quick query to grab the row in the User table with that user id that was stored in the session variable. It's a value that will never change, no matter what the user does. So I can know for 100% certainty that the value given will be an integer and will be a number that exists in the User table.

 

Is that a sufficient enough example of how I can know for certain?

Edited April 16, 2013 by dannyb785

[Jessica]

Jessica, your signature says that instead of giving answers to questions,you give hints. But just saying "it's not efficient" isn't helpful in the slightest.

YOU ASKED FOR SOMEONE TO SAY YES OR NO

 

Are you on DRUGS man?

[lemmin]

Yes, dannyb785, that query is completely fine to use. I personally don't know of any real-world situation where there would be a more efficient query to select a single row with one condition. Having said that, Jessica is right in that you should not be using quotes IF that column is an integer and you might also include a "LIMIT 1" at the end to ensure that only one row is returned.

 

As far as the code, I agree with everything else said; Always check for errors, and extract() is not the best practice.

 

If you are looking for highest efficiency of the code, you would obviously not use extract(), and mysql_fetch_array() with the MYSQL_NUM flag is quite a bit more efficient than mysql_fetch_assoc(). Of course, you won't get the named indices that way.

[dannyb785]

YOU ASKED FOR SOMEONE TO SAY YES OR NO

 

Are you on DRUGS man?

 

Your response of "no" was CLEARLY being sarcastic at my frustration. 

[dannyb785]

Yes, dannyb785, that query is completely fine to use. I personally don't know of any real-world situation where there would be a more efficient query to select a single row with one condition. Having said that, Jessica is right in that you should not be using quotes IF that column is an integer and you might also include a "LIMIT 1" at the end to ensure that only one row is returned.

 

As far as the code, I agree with everything else said; Always check for errors, and extract() is not the best practice.

 

If you are looking for highest efficiency of the code, you would obviously not use extract(), and mysql_fetch_array() with the MYSQL_NUM flag is quite a bit more efficient than mysql_fetch_assoc(). Of course, you won't get the named indices that way.

 

 

Praise Allah! Finally a response that was helpful. Thank you lemmin, you've restored my hope in this website

[ignace]

If you are looking for highest efficiency of the code..

..you would write it in Assembler. Can't get more efficient then that.

[dannyb785]

..you would write it in Assembler. Can't get more efficient then that.

 

I have no clue what Assembler is. I googled it and none of the results seem to be related to what I was asking. Can you expand your answer so it's actually helpful and not a waste of space?

[lemmin]

..you would write it in Assembler. Can't get more efficient then that.

 

Not if your assembly code is inefficient.

 

 

I was referring to a situation where pure speed takes priority over coding standards or readability. I always use mysql_fetch_assoc() for simplicity, but I've written scripts where it was necessary to sacrifice this for the completion time.

[Q695]

It's:

SELECT *
FROM `table`
WHERE `column` =value
AND `column` =value
LIMIT 0 , 30

 

then you tie the output into a where loop like this

 

while ($row) {
//echo this data

}

[Q695]

I have no clue what Assembler is. I googled it and none of the results seem to be related to what I was asking. Can you expand your answer so it's actually helpful and not a waste of space?

hand code is a good assembeler.

 

on WAMP I use PHPmyadmin .  very good for database compairing, and you can test atabase scripts in it.

[ignace]

I have no clue what Assembler is. I googled it and none of the results seem to be related to what I was asking. Can you expand your answer so it's actually helpful and not a waste of space?

Your question is already answered by Jessica. It's not efficient, because PHP is still interpreted to C(++?) which is itself compiled down to assembly to be executed by the CPU. You are standing on top of the empire state building and asking if you are close to the ground.

 

It's not efficient because there is more to efficiency then just the number of cpu cycles. Developing software is a trade-off between readability/maintainability and performance. PHP is an interpreted language which means it is executed statement after statement by the php binary on each run. This translation takes time, if your script contains any require/include lines it will use a disk seek, which is like we all know, SLLLLLLOOOOOOOOOOOWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW, but not by a magnitude you'll ever notice.

 

All of these things add up to being unable to serve just one or two more requests. But as a business you are not looking to serve one or two more people, you are looking to serving thousands more and no matter how good your code, how optimized your queries, you just can't get a thousand more out of it.

 

Do yourself and those you work with, and those that will work on your code after you a service, and write clean/good code.

Edited April 17, 2013 by ignace

[Jessica]

I believe the user got banned for this and other antagonistic behavior, so maybe we can lock this thread?