I can't for the life of me move from mysql_* to prepared queries

[r3wt]

am i stupid?

 

i've been programming for a while, and i'm decent to say the least, but how in the hell am i supposed to move to prepared queries, let alone process the results.

 

here is an example of my problem:

 

$sqlx = mysql_query("SELECT * FROM Wallets WHERE `disabled`='0' AND `Market_Id`='1' ORDER BY `Acronymn` ASC");
					$n_rows = mysql_num_rows($sqlx);
					for($nn = 0; $nn < $n_rows; $nn++) {
						$coinid = mysql_result($sqlx,$nn,"Id");
						$base   = mysql_result($sqlx,$nn,"Market_Id");
						if($base == '1') {
							$pair = "BTC";
						}
						if($base == '4') {
							$pair = "LTC";
						}
						if($base == '3') {
							$pair = "OSC";
						}
						$coinnm = mysql_result($sqlx,$nn,"Name");
						$coinac = mysql_result($sqlx,$nn,"Acronymn");
						$sqls_2 = @mysql_query("SELECT * FROM Trade_History WHERE `Market_Id`='".$coinid."' ORDER BY Timestamp DESC");
						$last_trade = @mysql_result($sqls_2,0,"Price");
						if($last_trade > 9){
							$p2disp = round($last_trade);
						}else{
							$p2disp = sprintf("%2.8f",$last_trade);
						}
						?>
						<li class='box2'><p title="Trade <?php echo $coinnm; ?>" onclick="window.location = 'index.php?page=trade&market=<?php echo $coinid; ?>&base=<?php echo $base; ?>';"><?php echo $coinac.' / '.$pair;?><br/><?php echo $p2disp; ?></p></li>
						<?php
					}

if you'll notice, i love to use mysql result and iteration frequently where possible for the sheer speed at which this operates. however i also struggle with how i would be able to compare with while($row = mysql_fetch_assoc($sql)){ //script} method if i were to use another approach. i noticed when using mysqli through a tutorial i read, its easy to do foreach($key as $value) type of loop, but i really do not like this method.

 

so it begs the question(due to my experience being only gained through trial and error mind you, i am not college educated) should i just continue to use mysql_* for queries that do not require user input? and whenever they do require userinput, why do my scripts always fail with prepared queries? 

 

i just don't seem to get it and its driving kinda crazy.

 

Any tips or advice is greatly appreciated. we all gotta learn somewhere, and i would rather not ask again at stackoverflow. rarely is anyone helpful there. seems like a place for old trolls to game up on noob programmers and ridicule them.

 

[ginerjm]

Using MySQL_result was a needless resource eater and you should not have been using it in this particular case at all.  If you are going to process the whole record you should be grabbing  it in one call.  How you 'catch' it is easily accommodated by using php's 'list' function which would have given you var names to use and you wouldn't have to have written your code this way.

 

And despite your misgivings about your existing designs, you will have to migrate away from MySQL_* functions in the near future.  Time to BTB.

[.josh]

Time to BTB.

 

 

Be The Ball?  Ah okay, a euphemism for focusing on the problem at hand.  CONCENTRATE.

 

Buy Ten Burritos?  Nope, not working.. maybe you're just hungry and can't focus on an empty stomach. Good call.

 

Bang The Blonde?  Still not gettin' it.  Okay, just walk way for a bit and come back to the problem later.  What an excellent distraction!

 

Beat The Beast? Okay fair enough; reality and all that... an excellent alternative if you don't have a blonde handy (or a handy blonde)

 

Bork The Bjork? I don't even know what the...maybe the fetish sites are too much of a distraction.  You've still got a problem to deal with here..

 

Bring The Beer? Still can't figure it out.. time to drown your sorrows. 

 

Blaze The Blunt?  In case you think booze tastes like carbonated piss.. 

 

Bob The Builder?  Okay fuck it.. time to hire a freelancer.  But can he fix it? Yes, yes he can. 

[mac_gyver]

for prepared queries, using the PDO library results in much cleaner code than using mysqli.

 

for queries that don't have any external input values, using a prepared query is a waste of resources. a prepared query requires two 'round-trip'/communication exchanges with the database server, one to send the query to be prepared and a second to send the bound data and execute the query.

 

your two queries in the posted code can and should be written as one join'ed query. running queries inside of loops is a performance killer since the communication needed to send the query statement (it's a string of characters) or bound data for a prepared query (each one is a string of characters and afaik binary data transfer isn't yet being used) to the database server is longer than the time it takes to execute most straightforward/basic queries.

 

the only time a msyql_result() is as efficient as using a fetch_assoc/row/array statement is if you are fetching ONE column from a query. mysql_result() always preforms a data seek, followed by a fetch, even if you are fetching multiple columns from the same row. using multiple mysql_result() statements takes proportionally more time than using one fetch_assoc/row/array statement.

 

there isn't a mysqli version of the mysql_result, probably due to the performance issue (and writing a function to emulate it is going to be about four times slower then a fetch_assoc/row/array statement) and there isn't an exact functional equivalent in PDO (there is a fetch column method that fetches a numbered column from a row and advances the row pointer and a fetch mode that will fetch a specific column from all rows.)

[kicken]

if you'll notice, i love to use mysql result and iteration frequently where possible for the sheer speed at which this operates.

This is going to be much slower than the usual fetch methods. Not sure where you got the idea that it is better. Each time you call a function you have a fair bit of overhead. Using mysql_result like you are means you're making 4 function calls each row when you really only need one, plus you have the extra num_rows call which would be unnecessary using the traditional fetch loop.

 

i noticed when using mysqli through a tutorial i read, its easy to do foreach($key as $value) type of loop, but i really do not like this method.

I recommend using PDO rather than mysqli. It's api is much simpler and functions much more similar to traditional mysql_* fetch loops than mysqli does.

 

Any tips or advice is greatly appreciated. we all gotta learn somewhere, and i would rather not ask again at stackoverflow. rarely is anyone helpful there. seems like a place for old trolls to game up on noob programmers and ridicule them.

You also need to learn more about SQL and joins, doing another query while processing the results of the previous query is pretty much always a terrible idea. Most of the time you can do what you want in just one query with a JOIN.

 

Also, do not use SELECT *. List out your columns that you need. Using * makes the code less readable and your queries less efficient.

 

<?php

//$db is an instance of PDO

$sql = "
SELECT 
	w.Id
	, w.Market_Id
	, w.Name
	, w.Acronymn
	, price.Price
FROM Wallets w
INNER JOIN (
	SELECT history.Market_Id, history.Price
	FROM Trade_History history
	INNER JOIN (SELECT Market_Id, MAX(Timestamp) Timestamp FROM Trade_History) mostRecent 
		ON mostRecent.Market_Id=history.Market_Id AND mostRecent.Timestamp=history.Timestamp
) price ON price.Market_Id=w.Id
WHERE 
	w.disabled='0' 
	AND w.Market_Id='1' 
ORDER BY 
	w.Acronymn ASC
";
$sqlx = $db->query($sql);
$sqlx->setFetchMode(PDO::FETCH_ASSOC);
foreach ($sqlx as $row){
	$coinid = $row["Id"];
	$base   = $row["Market_Id"];
	if($base == '1') {
		$pair = "BTC";
	}
	if($base == '4') {
		$pair = "LTC";
	}
	if($base == '3') {
		$pair = "OSC";
	}
	$coinnm = $row["Name"];
	$coinac = $row["Acronymn"];
	$last_trade = $row['Price'];
	if($last_trade > 9){
		$p2disp = round($last_trade);
	}else{
		$p2disp = sprintf("%2.8f",$last_trade);
	}
	?>
	<li class='box2'><p title="Trade <?php echo $coinnm; ?>" onclick="window.location = 'index.php?page=trade&market=<?php echo $coinid; ?>&base=<?php echo $base; ?>';"><?php echo $coinac.' / '.$pair;?><br/><?php echo $p2disp; ?></p></li>
<?php
}
?>

[ginerjm]

Be The Ball?  Ah okay, a euphemism for focusing on the problem at hand.  CONCENTRATE.

 

Buy Ten Burritos?  Nope, not working.. maybe you're just hungry and can't focus on an empty stomach. Good call.

 

Bang The Blonde?  Still not gettin' it.  Okay, just walk way for a bit and come back to the problem later.  What an excellent distraction!

 

Beat The Beast? Okay fair enough; reality and all that... an excellent alternative if you don't have a blonde handy (or a handy blonde)

 

Bork The Bjork? I don't even know what the...maybe the fetish sites are too much of a distraction.  You've still got a problem to deal with here..

 

Bring The Beer? Still can't figure it out.. time to drown your sorrows. 

 

Blaze The Blunt?  In case you think booze tastes like carbonated piss.. 

 

Bob The Builder?  Okay fuck it.. time to hire a freelancer.  But can he fix it? Yes, yes he can. 

Some very nice choices, but 'twas much more to the point: Bite The Bullet.

[r3wt]

its funny to look back and see how dumb i was

[Psycho]

I'll second all of the preceeding comments. I'm at a loss as to how you ended up with that code considering you are a "decent" programmer. Sorry if that is harsh, but there's nothing worse than someone who overestimates their own abilities. It is from knowing your weakness that people are open to improvement. I hope you take the comments here as constructive criticism to improve. I *think* the issue is that you have gotten into a comfort zone using functions and processes that you learned to use and rather than learn new and better ways to accomplish things you keep falling back to what you know.

 

So, having said that, I see something in the code above that doesn't make sense. The table 'Wallets' apparently has an Id field and a Market_Id field. Ok, The Trade_History table also has a field named Market_Id. Ok, no problem. But, when you are running the inner query (which you shouldn't since you should have a JOIN) you are getting records from the Trade_History table where the Market_Id in that table matches the Id from the 'Wallets' table. That doesn't make sense. It would seem appropriate to associate the records based on the Market_Id. Either you are using the wrong value or the names of the fields in the tables are less than desirable.

 

One other tip. The code above uses a lot of lines just to retrieve individual values and assign to variables. There's no need to assign something to a variable if it is only going to be used one on the next line or two.