Too many Queries

Damond · February 14, 2015

I know there is a way.... Can some one show me how to make this much MUCH shorter?

$invs = $db->fetch_row($db->query("SELECT * FROM inventory WHERE inv_userid = {$ir['userid']} AND inv_itemid = 84"));
$invs2 = $db->fetch_row($db->query("SELECT * FROM inventory WHERE inv_userid = {$ir['userid']} AND inv_itemid = 87"));
$invs3 = $db->fetch_row($db->query("SELECT * FROM inventory WHERE inv_userid = {$ir['userid']} AND inv_itemid = 90"));
$invs4 = $db->fetch_row($db->query("SELECT * FROM inventory WHERE inv_userid = {$ir['userid']} AND inv_itemid = 93"));
$invs5 = $db->fetch_row($db->query("SELECT * FROM inventory WHERE inv_userid = {$ir['userid']} AND inv_itemid = 96"));
$invs6 = $db->fetch_row($db->query("SELECT * FROM inventory WHERE inv_userid = {$ir['userid']} AND inv_itemid = 99"));

if ($invs['inv_itemid'] == 84 && $invs['inv_qty'] > 0){
$on_hand = $invs['inv_qty'];
} else {
$on_hand = 'None';
}
if ($invs2['inv_itemid'] == 87 && $invs2['inv_qty'] > 0){
$on_hand2 = $invs2['inv_qty'];
} else {
$on_hand2 = 'None';
}
if ($invs3['inv_itemid'] == 90 && $invs3['inv_qty'] > 0){
$on_hand3 = $invs3['inv_qty'];
} else {
$on_hand3 = 'None';
}
if ($invs4['inv_itemid'] == 93 && $invs4['inv_qty'] > 0){
$on_hand4 = $invs4['inv_qty'];
} else {
$on_hand4 = 'None';
}
if ($invs5['inv_itemid'] == 96 && $invs5['inv_qty'] > 0){
$on_hand5 = $invs5['inv_qty'];
} else {
$on_hand5 = 'None';
}
if ($invs6['inv_itemid'] == 99 && $invs6['inv_qty'] > 0){
$on_hand6 = $invs6['inv_qty'];
} else {
$on_hand6 = 'None';
}

There are so many items in the inventory and we are trying to target these 6. I have been reading about arrays and imploding and exploding and my damn head is about to explode..

Thanks in advance. I always get the best advice here.

Sim · February 14, 2015

Something like this =]

$items = "84, 87, 90, 93, 96, 99";

$res = $db->query("SELECT * FROM inventory WHERE inv_userid = {$ir['userid']} AND inv_itemid IN($items)");

while($inv = $db->fetch_row($res))
{
if ($invs['inv_itemid'] == 84 && $invs['inv_qty'] > 0){
   $on_hand = $invs['inv_qty'];
} else {
   $on_hand = 'None';
}
if ($invs2['inv_itemid'] == 87 && $invs2['inv_qty'] > 0){
   $on_hand2 = $invs2['inv_qty'];
} else {
   $on_hand2 = 'None';
}
if ($invs3['inv_itemid'] == 90 && $invs3['inv_qty'] > 0){
   $on_hand3 = $invs3['inv_qty'];
} else {
   $on_hand3 = 'None';
}
if ($invs4['inv_itemid'] == 93 && $invs4['inv_qty'] > 0){
   $on_hand4 = $invs4['inv_qty'];
} else {
   $on_hand4 = 'None';
}
if ($invs5['inv_itemid'] == 96 && $invs5['inv_qty'] > 0){
   $on_hand5 = $invs5['inv_qty'];
} else {
   $on_hand5 = 'None';
}
if ($invs6['inv_itemid'] == 99 && $invs6['inv_qty'] > 0){
   $on_hand6 = $invs6['inv_qty'];
} else {
   $on_hand6 = 'None';
}
}

Damond · February 14, 2015

[MENTION=64603]Sim[/MENTION]

While much cleaner for some reason all of them are showing as 'None'

I did change all my IF statements to match the $inv.

Sim · February 14, 2015

[MENTION=66368]Damo[/MENTION]n, this line is prob the issue:

$db->fetch_row(

It might need to be changed to something like $db->fetch_array(

or something similar. I am not familar with the mC-code database class. ;\

Damond · February 14, 2015

[MENTION=64603]Sim[/MENTION]

We tried

while($inv = mysqli_fetch_array($res))

But it gives the same result. I was wrong before in saying they are all saying 'none' the last one is actually reading out correctly.

jcvenom · February 14, 2015

Why are you fetching row at the same time using $db->query? try

 mysql_fetch_assoc($db->query(""));

KyleMassacre · February 15, 2015

$items = array(84, 87, 90, 93, 96, 99);
//your db query....
//you while statement....
if (in_array($res['inv_itemid']) && $res['inv_qty'] == 0) {
   echo "Empty";
}

Coly010 · February 15, 2015

$items = array(84, 87, 90, 93, 96, 99);
//your db query....
//you while statement....
if (in_array($res['inv_itemid']) && $res['inv_qty'] == 0) {
   echo "Empty";
}

Not sure if you understand what Kyle is doing here but I'm going explain two ways that it could work. Use Sim's query to pull only the specific items from the database or just pull the users entire inventory, then whilst it iterates through it if the item Id of one of the specified items appears it'll trigger the if statement. That way you can do whatever code you like then with it

KyleMassacre · February 15, 2015

Not sure if you understand what Kyle is doing here but I'm going explain two ways that it could work. Use Sim's query to pull only the specific items from the database or just pull the users entire inventory, then whilst it iterates through it if the item Id of one of the specified items appears it'll trigger the if statement. That way you can do whatever code you like then with it

Yep :p sorry I'm just on my phone so I couldn't really get into the details and I should have said this was based off [MENTION=64603]Sim[/MENTION]'s post a little bit

Sim · February 15, 2015

Why are you fetching row at the same time using $db->query? try
 mysql_fetch_assoc($db->query("")); 

query() gets all the results you are wanting. statements such as mysql_fetch_assoc() mysql_fetch_array(), mysql_fetch_row() gets what you want from the returned query... ;\

Veramys · February 15, 2015

Why are you fetching row at the same time using $db->query? try
 mysql_fetch_assoc($db->query("")); 

First we're not using mysql. We're using the mysqli class. And second straight from mysqli class:

function fetch_row($result=0)
 {
   if(!$result) { $result=$this->result; }
   return mysqli_fetch_assoc($result);
 }

So why not fetch at the same time as querying? There isn't anything wrong with doing it that way.

jcvenom · February 15, 2015

query() gets all the results you are wanting. statements such as mysql_fetch_assoc() mysql_fetch_array(), mysql_fetch_row() gets what you want from the returned query... ;\

Thanks for that I've never used mysql_fetch_row before what's it's use?

Sim · February 15, 2015

mysql_fetch_row retrieves 1 row from the query [MENTION=69823]jcvenom[/MENTION]

jcvenom · February 15, 2015

First we're not using mysql. We're using the mysqli class. And second straight from mysqli class:
function fetch_row($result=0)
 {
   if(!$result) { $result=$this->result; }
   return mysqli_fetch_assoc($result);
 }
So why not fetch at the same time as querying? There isn't anything wrong with doing it that way.

sorry i didn't read the code fully as im on my phone

KyleMassacre · February 15, 2015

First we're not using mysql. We're using the mysqli class. And second straight from mysqli class:
function fetch_row($result=0)
 {
   if(!$result) { $result=$this->result; }
   return mysqli_fetch_assoc($result);
 }
So why not fetch at the same time as querying? There isn't anything wrong with doing it that way.

Well yes and no if you want it easy to read I suppose in reality, though I have never tried, you could do something like this:

while($result = $db-fetch_row($db->query("SELECT `soomething` FROM `somewhere`))) {
   //continue with your loop
}

To me that looks ugly as hell and you may lose some functionality and control of certain aspects of your results. For example what are you to do if your while() statement returns FALSE? You see, now you lost control unless you wanted to throw it in a try/catch block instead of a simple if/else block.

That is why (for this example) fetching your query right off the bat may not be the best idea because you lose all your data in the loop outside of the loop if you require to use it

Sim · February 15, 2015

Well yes and no if you want it easy to read I suppose in reality, though I have never tried, you could do something like this:
while($result = $db-fetch_row($db->query("SELECT `soomething` FROM `somewhere`))) {
   //continue with your loop
}
To me that looks ugly as hell and you may lose some functionality and control of certain aspects of your results. For example what are you to do if your while() statement returns FALSE? You see, now you lost control unless you wanted to throw it in a try/catch block instead of a simple if/else block.

That is why (for this example) fetching your query right off the bat may not be the best idea because you lose all your data in the loop outside of the loop if you require to use it

Not sure if that will work anyway. Wouldn't it just requery on each execution? So you'd be starting from the beggining?

KyleMassacre · February 15, 2015

Not sure if that will work anyway. Wouldn't it just requery on each execution? So you'd be starting from the beggining?

Wouldn't know lol, I have never tried it that way but now that you mention it seems like it wouldn't work

Sim · February 15, 2015

Wouldn't know lol, I have never tried it that way but now that you mention it seems like it wouldn't work

I can't remember the last time Ive done it that way, and if its been that long ago, I would think the reason I don't do it is because it don't work -_-

lucky3809 · February 15, 2015

Of course it does not work for one the $vars are incorrect in the IF statements look closer to Sims recoded part, there is NO use any longer for the $vars; $invs,$invs2, and so forth they should all become $inv as stated in the WHILE statement...


This is Sims recoded part...

$items = "84, 87, 90, 93, 96, 99";
$res = $db->query("SELECT * FROM inventory WHERE inv_userid = {$ir['userid']} AND inv_itemid IN($items)");

while($inv = $db->fetch_row($res))
{
if ($invs['inv_itemid'] == 84 && $invs['inv_qty'] > 0){
   $on_hand = $invs['inv_qty'];
} else {
   $on_hand = 'None';
}
if ($invs2['inv_itemid'] == 87 && $invs2['inv_qty'] > 0){
   $on_hand2 = $invs2['inv_qty'];
} else {
   $on_hand2 = 'None';
}
if ($invs3['inv_itemid'] == 90 && $invs3['inv_qty'] > 0){
   $on_hand3 = $invs3['inv_qty'];
} else {
   $on_hand3 = 'None';
}
if ($invs4['inv_itemid'] == 93 && $invs4['inv_qty'] > 0){
   $on_hand4 = $invs4['inv_qty'];
} else {
   $on_hand4 = 'None';
}
if ($invs5['inv_itemid'] == 96 && $invs5['inv_qty'] > 0){
   $on_hand5 = $invs5['inv_qty'];
} else {
   $on_hand5 = 'None';
}
if ($invs6['inv_itemid'] == 99 && $invs6['inv_qty'] > 0){
   $on_hand6 = $invs6['inv_qty'];
} else {
   $on_hand6 = 'None';
}
}

Edited February 15, 2015 by lucky3809

Sim · February 15, 2015

The loop should also ref while mysql_fetch_assoc(), not fetch_row

while ($inv = mysql_fetch_assoc($res))

{

KyleMassacre · February 15, 2015

Of course it does not work for one the $vars are incorrect in the IF statements look closer to Sims recoded part, there is NO use any longer for the $vars; $invs,$invs2, and so forth they should all become $inv as stated in the WHILE statement...


This is Sims recoded part...

$res = $db->query("SELECT * FROM inventory WHERE inv_userid = {$ir['userid']} AND inv_itemid IN($items)");

while($inv = $db->fetch_row($res))
{
if ($invs['inv_itemid'] == 84 && $invs['inv_qty'] > 0){
   $on_hand = $invs['inv_qty'];
} else {
   $on_hand = 'None';
}
if ($invs2['inv_itemid'] == 87 && $invs2['inv_qty'] > 0){
   $on_hand2 = $invs2['inv_qty'];
} else {
   $on_hand2 = 'None';
}
if ($invs3['inv_itemid'] == 90 && $invs3['inv_qty'] > 0){
   $on_hand3 = $invs3['inv_qty'];
} else {
   $on_hand3 = 'None';
}
if ($invs4['inv_itemid'] == 93 && $invs4['inv_qty'] > 0){
   $on_hand4 = $invs4['inv_qty'];
} else {
   $on_hand4 = 'None';
}
if ($invs5['inv_itemid'] == 96 && $invs5['inv_qty'] > 0){
   $on_hand5 = $invs5['inv_qty'];
} else {
   $on_hand5 = 'None';
}
if ($invs6['inv_itemid'] == 99 && $invs6['inv_qty'] > 0){
   $on_hand6 = $invs6['inv_qty'];
} else {
   $on_hand6 = 'None';
}
}

Well damn, how can we miss that lol good eye. Regardless there is too much repeated code. Using in_array() to search an array for a key/value pair would probably IMO be the best way

- - - Updated - - -

The loop should also ref while mysql_fetch_assoc(), not fetch_row
while ($inv = mysql_fetch_assoc($res))

{

fetch_row() is *_fetch_assoc() if you are and should be using the $db object and not mysql* objects/functions

Sim · February 15, 2015

[MENTION=64603]Sim[/MENTION]
While much cleaner for some reason all of them are showing as 'None'

I did change all my IF statements to match the $inv.

You all just forgot he mentioned he did that already [MENTION=65518]Kyle[/MENTION]

Veramys · February 15, 2015

I don't see how you would lose functionality [MENTION=68711]KyleMassacre[/MENTION] all that doing it $db->fetch_row($db->query( does is cut down on using two lines to do what you can do on one.

Why have this:

$q = $db->query("SELECT * FROM users WHERE userid=$userid");
$r = $db->fetch_row($q);

When this is easier:

$r = $db->fetch_row($db->query("SELECT * FROM users WHERE userid=$userid"));

Sim · February 15, 2015

I don't see how you would lose functionality [MENTION=68711]KyleMassacre[/MENTION] all that doing it $db->fetch_row($db->query( does is cut down on using two lines to do what you can do on one.
Why have this:
$q = $db->query("SELECT * FROM users WHERE userid=$userid");
$r = $db->fetch_row($q);
When this is easier:
$r = $db->fetch_row($db->query("SELECT * FROM users WHERE userid=$userid"));

That is fine for 1 result.. Multiple results from the query will produce unwanted outcomes when looping $db->fetch_row($db->query("SELECT * FROM users WHERE userid=$userid"));

KyleMassacre · February 15, 2015

You all just forgot he mentioned he did that already @Kyle

tl;dr; :p

I don't see how you would lose functionality @KyleMassacre all that doing it $db->fetch_row($db->query( does is cut down on using two lines to do what you can do on one.
Why have this:
$q = $db->query("SELECT * FROM users WHERE userid=$userid");
$r = $db->fetch_row($q);
When this is easier:
$r = $db->fetch_row($db->query("SELECT * FROM users WHERE userid=$userid"));

Lets say you run $r in a loop but $r returns false, how would you handle that? This is why it is a bit better in most cases to query your database then fetch the results seperately:

//Query our db
$q = $db->query("SELECT * FROM users WHERE userid=$userid");
//Are there any rows from our query?
if($db->num_rows($q)) {
//Yes, lets loop through them
   $results = array();
   while($r = $db->fetch_row($q)) {
       $results[] = $r;
   }
   echo $results."\n\r";
}
//Nope, no rows lets inform them
else {
   echo "No results found!";
}

Opposed to:

$r = $db->fetch_row($db->query("SELECT * FROM users WHERE userid=$userid"));
results = array();
while($r) {
   $results[] = $r;
} 
echo $results."\n\r";

In all honesty I think the first one looks much cleaner and easier to read even though its more than twice the lines but you have more control over the output much easier than stacking the methods together

Edited February 15, 2015 by KyleMassacre
fixed syntax error

Too many Queries

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation