basic SQL queries

[DaveC426913]

Some pretty basic questions about mySQL/PHP.

I'm trying to get my head around what is getting returned from mysql_query's.

I'm sure what I'm using is terribly inefficient.

1] looping through rows:

OK, this is how I've seen it done, but I don't really understand it. The while statement is automagically incrementing by a row each time?


$sql = "SELECT `*` FROM tbl1 ORDER BY `MDate` DESC";
$resultA = mysql_query($sql);
while ($RevRow = mysql_fetch_array($resultA)) {
}


2] Just one value from one row in the db

If I just want to get a single specific value out of the my db, do I need to get an array returned every time?

$sql = "SELECT `CName` FROM tbl2 WHERE `CCount` = '".$RevRow['MCategory1']."'";
$resultB = mysql_query($sql);
$catName = mysql_result($resultB,0);


3] How many rows?

Is there a more streamlined method than this just for counting the rows I want? I don't need any data right now.

$sql = "SELECT `*` FROM tblReviewsMain WHERE `MRating` = '".$rat."'";
$resultD = mysql_query($sql);
$numRows = mysql_num_rows($resultD);

Thx.

 

[Homersim2600]

On number 1 you are missing the line

$RevRow++;

This is what adds 1 to itself.

On Number 2 the answer is NO.

I am a newbie myself so I may be incorrect, but I think if you simply did this...

$sql = "SELECT `CName` FROM tbl2 WHERE `CCount` ='MCategory1'";

...then I think it would work. Again, I am a bit of a newbie so I may be incorrect, but it is worth try.

Number 3,
I guess you could add a field that denotes a number for each record, that is autoincrementing, and then just query for the highest numbered record.

For Instance...
$query="SELECT * FROM contacts Order by 'id' desc";

where 'id' = the autoincrementing field
From here all you would have to do is pull the first record from the top, and read the 'id' field. However, there is one caveat to this, and that is that if a record is ever deleted, then your 'id' field would not update the proper amount of records in the database. What's wrong with the method you posted?


LF



"As far as the laws of mathematics refer to reality, they are not certain; as far as they are certain, they do not refer to reality."--Albert Einstein

 

[DaveC426913]

1]
" missing $RevRow++;
This is what adds 1 to itself."

Yeah, except this line doesn't appear to be necessary.

2]
"$sql = "SELECT `CName` FROM tbl2 WHERE `CCount` ='MCategory1'";"

Yes, but then what? That returns the data in a fomat I can't use. It sez "resource id#5".


3]
"What's wrong with the method you posted?"

*Two* SQL queries?
There's got to be a more efficient way.

 

[ingresman]

1. Can you illustrate what is wrong your posted code just loops aroud the record set and does nothing.
2.Yes the result from the mysql_query is a mysql recordset which is managed by PHP as an exntention resouce. (if you try to echo the result set name e.g. echo $resultA you will get something like #1. To sum up the resut set is the same whether it contains 1 or 26 rows.
3. you could use

$qry="SELECT count(*) as cnt FROM tblReviewsMain WHERE `MRating` = '".$rat."'";
$rs=mysql_query($qry);
$crow = mysql_fetch_object($rs);
echo $crow->cnt

hope this helps, and notice i use mysql_fetch_object()

 

[DaveC426913]

#1 looping through a query result

$sql = "SELECT DISTINCT * FROM tblLinks WHERE `LCategory`='".$catName."' AND (`LSubCat` IS NULL OR `LSubCat`='')";
$resultB = mysql_query($sql);
while ($LinkRow = mysql_fetch_array($resultB)) {
echo " <li>".$LinkRow['LPreText']." <a href='".$LinkRow['LURL']."' style='text-decoration: none;'>".$LinkRow['LLinkText']."</a> ".$LinkRow['LPostText']."<br>\n";
}

OK, the above code works fine, I pulled the loop logic from php.net. It loops without an explicit incrementer. I've been assuming the fetch_array takes care of that.

I'll just keep using this, since it works.

 

[DaveC426913]

#2 getting a single value

This is the one I do a lot and understand the least.

$sql = "SELECT `CName` FROM tblReviewsCategories WHERE `CCount` = '".$RevRow['MCategory1']."'";
$resultB = mysql_query($sql);
$catName = mysql_result($resultB,0);

The query is going to return only one field from one row. But it returns it in an array anyway. What is the easiest way to get the data?

 

[DaveC426913]

This leads to a general best practice question:

Which is better code practice, all other things being equal?
- make one query on a page, get all the data you'll need, and then manipulate it once you have it in a big array, do your derived calcs (eg. sums, counts) on the array
- keep the data in the database, call only for data you need, do your derived calcs (eg. sums, counts) on the db data

(we'll assume only one user, no multi-user multi-updating issues here)

 

[jpadie]

on #2
i don't think it is fair to say that the mysql_query function returns an array. Better to say that it returns a results set and the set can be empty or otherwise.

the method you use to grab the data is fine. I personally always use mysql_fetch_assoc (not mysql_fetch_array) just because I don't find it a major performance drag on any of my applications and the consistency of coding approach just means less debugging later.

btw. after calling mysql_fetch_assoc i mostly call extract() to remove the variables from the row array, and then unset the row array to free the memory - just a coding preference rather than a statement of best practice.

on best practice, some thoughts:

mysql is rippingly fast for most things and particularly compared to other performance factors like bandwidth, browser performance and web server performance (incl PHP). I find myself often creating cascading myswl queries rather than spending the time to work out a sql expression to do it in one query.

thus for pure performance i'd recommend the second option. for ease of coding i'd use php to do the data manipulation - apart from anything else it is easier, in my opinion, to debug). but i would not necessarily try to grab all the data for the whole of your page in one chunk. grab what you need for each functional element.

this was not the case a few years ago where sql optimisation was far more in vogue. the cost of hardware etc has fallen so much that it is now cheaper to shove extra memory or even an extra box into your hardward structure than to pay a consultant to spend a day or so optimising your sql (with all the consequent functional and regression testing and debuggin etc...).

hth
Justin