Page 1 of 1
SELECT *JUST* one record from database?!?
Posted: Sat May 15, 2010 10:52 am
by drayfuss
Hey,
I'm aware of how to select records from a database, and pull them into an associative or numerated array. But what's the most efficient way of getting the result of ONE cell of a database table based on a WHERE clause.
Code: Select all
$result = mysql_query($query);
if($result){
echo('>'.$result.'<');
return mysql_result($result, 0, $field);
}else{
# Error - no existing field or value.
return 0;
}
The above code works only if there is an existing value based on the WHERE clause in my query. If there is no existing value it throws an error:
Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 4 in /Applications/XAMPP/xamppfiles/htdocs/mvc/Database.php on line 181.
The error section of my script (# Error - no existing field or value. return 0;) never gets called because $result always returns true, even if the conditions of the query are not met.
I can't believe PHP doesn't have a simple cell retrieval function for mysql.
Any thoughts?
drayfuss
Re: SELECT *JUST* one record from database?!?
Posted: Sat May 15, 2010 12:28 pm
by califdon
I must presume that you are new to database concepts. The term "cell" has no meaning for a database. A table has
rows and
columns. That's more than just a matter of terminology, it is central to the concept of how data is stored and retrieved. Thinking in terms of the intersection of a row and a column, which I assume is what you mean by "cell", will lead you in the wrong direction.
The customary syntax for retrieving data in PHP would look more like this:
Code: Select all
$sql = "SELECT * FROM xxxxx WHERE yy = zz";
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_assoc($result)) {
// process this row
}
The condition for the
while block to execute is the result of calling the mysql_fetch function. If it returns False (no row to return), your code never tries to operate on it and you never get an error. If there are no rows returned at all, you can test for that condition with mysql_num_rows() and take appropriate action. If there is one row returned, it will be processed. If there are many rows returned, they will be processed in sequence until there are no more rows.
Re: SELECT *JUST* one record from database?!?
Posted: Sat May 15, 2010 2:25 pm
by drayfuss
Thanks for the reply.
I understand the wanting not to use the word 'cell' for purposes of not confusing how mysql works, but surely we can all admit to ourselves that 'cell' is the perfect word to describe an individual space on the metaphorical 'table'.
I understand what you mean about the technique in getting a single piece of data from a specific place on the database. But I'm still surprised that there's not an easier way to grab one entry in a mysql table, without using a list of mysql_fetch and mysql_num_rows functions.
Anyhow, I guess I'll just have to rely on these functions.
drayfuss
Re: SELECT *JUST* one record from database?!?
Posted: Sat May 15, 2010 3:21 pm
by califdon
You can use any terminology you want, but you will not be understood in the database community if you use the term "cell" when referring to a database table. There is a strategically important difference between a database table and an HTML table (or, Heaven forbid, a spreadsheet table!). Unfortunately, we use the same word to describe several things that are not closely related. Although we sometimes display data that was originally stored in a database table, in an HTML table, we just as often manipulate the data and the way it is displayed it is NOT in any relationship to the way it was stored. Unless you keep these two things separate in your mind, you will have a lot of difficulty working with databases. This is why terminology can be so useful, it helps shape how you think about things. Web designers and database developers sometimes have trouble communicating clearly because they think in terms of different aspects of the project. Here, we tend to be PHP coders and database developers, so we use database terminology and we sometimes downplay how the data will be displayed. So I would not agree that "cell" is a useful word in talking about a database table. It leads to thinking in terms of a single piece of data, and that kind of thinking doesn't help in designing or using a database, where a single piece of data has nothing to do with what it might look like when arranged in some conventional display pattern. Rows and columns have distinct and critically important meanings. Cells do not.
Again, what is an "entry"? That could mean anything or it could mean nothing. It could mean a single data value, or it could mean an entire record consisting of dozens of values. The value of a database lies in its ability to store large quantities of related data and being able to retrieve it reliably and quickly. When you ask about "grabbing" one "entry", I have no idea what you really mean. I would say that it's very easy to retrieve whatever data you need, based on identifiers stored with the data. Data is identified by what database, what table, what record ID, and what column or field it's stored in. I don't see how it could be simplified from that. The purpose of using a while() block in PHP is only to make it more general so that it will handle the 3 possible cases: no record returned, one record returned, or more than one record returned. If you have a situation where it can never return more than one record, you wouldn't use that programming approach, but that has nothing to do with the database, that's just your PHP code.
The fact that we're having this conversation at all reinforces what I'm saying about the need for very specific and clear terminology, and that database design and programming to access databases is not at all intuitive and if you are trying to learn how to do these things, you will be well advised to develop your ability to think in terms that have proven to be useful for the past 50 years or so. If these skills were not required to work with databases, believe me, they would have been tossed aside a long time ago.
Re: SELECT *JUST* one record from database?!?
Posted: Sat May 15, 2010 3:47 pm
by drayfuss
This has turned into a bit of a <span style='color:blue' title='I'm naughty, are you naughty?'>smurf</span> contest.
I fully agree that a good semantic field is useful for efficient communicado. But to refuse to ackowledge that a database table cannot be broken down further beyond columns and rows in the mind of a developer I surely just playing devils advocate.
As you've probably guessed, I'm not a database expert. But I do understand that mysql, natively, is just one long text file that indexes on a by-table, by-column, by-row basis. Can you meet me halfway and admit that, sometimes a developer wants the simplest route to a destination. In the event that I know I only want one piece of data from a table, I feel it would be helpful to have a strict method of fetching that data that doesn't involve a whileloop or an if statement. I just want to be able to return false, or the data therein.
Just to add, I'm in no way attempting to challenge your knowledge on databases. I'll wager you have far more experience than I do. But I find the best way for me to learn is to pressure the question until I've sorted what is nescessary knowlegde and what is just difference for the sake of difference.
Drayfuss
Re: SELECT *JUST* one record from database?!?
Posted: Sat May 15, 2010 4:47 pm
by califdon
I don't see this as any sort of a contest. I just try to lend a bit of help when I can, based on a long career of both teaching and commercial experience. I understand and am comfortable with your probing to reach answers that satisfy your needs.
It sounds to me as though you're thinking of a database in a similar manner to a file system directory, where there's an index from the name of a file to its physical position on a storage device. That's an analogy that will not serve you well. Relational databases are far more than that and (you'll have to trust me here) thinking that there's some kind of a shortcut will prove to be a constraint on your learning anything useful about them. The origin of relational databases is well documented (google it, especially the work of E. F. Codd at IBM around 1970) and is based on mathematical set theory, not physical storage. I had the good fortune, many years ago, to take a seminar from Codd's colleague and author of the seminal book on the subject, Chris Date, who emphasized this distinction.
Your desire to "simplify" the way data is stored and retrieved can be applied to simple files, although it doesn't really simplify anything. What is different is that relational databases like MySQL and all the others, exist because they can refer to stored data in many complex ways, namely, how some data is related to other data that is stored non-contiguously. So simply indexing some single piece of data just because that's all you need right at this moment doesn't make any sense. If that's all you need to do, don't use a relational database at all, just use a text file.
As I thought I explained, you do NOT need to use while() and if() syntax for particular unusual situations where you can positively predict what data is going to be present. You would do so only to assure that your application doesn't go belly-up the first time it encounters an anomaly, such as multiple records returned when you only expected one.
As I said, I am comfortable with someone probing to reach a true understanding, otherwise I wouldn't take the time to respond in some detail to your questions. At the same time, if someone is unwilling to accept advice coming from some degree of experience, thinking that their native intelligence is all that is required to reject well accepted principles, that can become a barrier to learning.
Re: SELECT *JUST* one record from database?!?
Posted: Sun May 16, 2010 12:27 am
by Benjamin
Code: Select all
public function getRow($query, $free_result = true) {
if ($this->query($query) && $this->result && $this->num_rows() > 0) {
$row = $this->fetch_assoc();
if ($free_result) { $this->free_result(); }
return $row;
}
return false;
}
Code: Select all
if (false !== $row = $db->getRow("SELECT field FROM table WHERE foo = 'bar'")) {
echo $row['field'];
}
Re: SELECT *JUST* one record from database?!?
Posted: Sun May 16, 2010 5:01 am
by drayfuss
Thanks for the code Benj.
Califdon,
I do really appreciate you taking the time to teach me about databases. But I don't understand why retrieving one field from a row in a database is not standard procedure? Where I don't want to store it in a text file is because that single piece of data IS related to the rest of the database. Just because there are times when I need it on it's own doesn't mean there aren't times when I need it to be part of a structured table.
I suppose I became a little critical that you were making a point that, although perfectly valid, didn't really need to be made as my request for a simple way of retrieving a single piece of data was a perfectly acceptable one.
I am interested in databases though, especially if that interest leads me to a better understanding of how I could implement and structure my code. Do you have any recommended reading for a beginner?
Jack
Re: SELECT *JUST* one record from database?!?
Posted: Sun May 16, 2010 1:36 pm
by califdon
I never implied that retrieving one column of one row of a table is not standard. Of course it is commonly done, on the basis of the WHERE clause of an SQL query statement, but not based on the concept of an "index", which is a concept that is used in spreadsheets (cell C14). Spreadsheets identify data by its position in a grid. Databases do not, and should not, because data is identified by its relationship to tables, row identifiers, columns, and other data in the database. That's the concept to understand.
With that understanding, it is usually very simple to retrieve a single piece of data. Tell me how to identify a piece of data, in the context of your data schema, and I will tell you the SQL statement to retrieve it. The exact syntax, including whether it is necessary to use a loop or a conditional, depends on a great many other factors in a particular application. There are also several alternative syntax constructs that can be used, such as the use of functions and object oriented abstractions, like Benjamin offered, just above. If you want to explain a specific requirement, perhaps I can be clearer. It is difficult to give you a direct answer without knowing what it is you're trying to do.
Re: SELECT *JUST* one record from database?!?
Posted: Sun May 16, 2010 2:03 pm
by califdon
I realized that I hadn't responded to your question about study materials for a beginner. Books range from heavy mathematics to trivial how-to, and I don't have a particular few to recommend. There are some pretty good online tutorials, among which I've scanned and can recommend these:
http://www.learn-sql-tutorial.com/DatabaseBasics.cfm (really basic!)
http://www.surfermall.com/relational/lesson_1.htm
http://java.sun.com/docs/books/tutorial ... abase.html
http://www.tizag.com/mysqlTutorial/
http://www.tekstenuitleg.net/en/article ... intro.html
http://www.phlonx.com/resources/nf3/