Querying off an array so to speak

[cooldude832]

I'm making a  game.

I have an inventory table that sorta looks like

ItemID

UserID

Type

Status

Attack

Defense

Magic

 

and to save db load i'm saying this for my query

<?php
$q = "SELECT * From `Items` Where UserID = $userid";
$item_r = mysql_query($q) or die(mysql_error());
while($item_row = mysql_fetch_array($item_r)){
foreach ($row as $key => $value){
$item[][$key] = $value;
}
}
?>

which works great, but then how can i query this array I made for items with status=1? Its probably really easy, but I want to do it with out looping through without a foreach.

 

[sKunKbad]

$q = "SELECT * From `Items` Where Status = 1";

[cooldude832]

but then i have to requery mysql which is what i'm trying to avoid, my idea is to run 1 query get alll items for that user and then deal with them afterwards, this game is very mysql strenious and I want to limit its load.

 

I was thinking array_serach(); but doesn't that loop through it.  I guess I can't really do it without looping through, anyone got a better idea

[trq]

o save db load i'm saying this for my query

 

That makes little sense. Database are made for querying data... why do you assume your putting too much load on it?

 

but I want to do it with out looping through

 

If you want to display the results you'll need a loop.

[sKunKbad]

multiple queries to mySQL never hurt me. I have pages with 6 or 7 on one page.

[trq]

[his game is very mysql strenious and I want to limit its load.

 

How do you know that? The more you can get done in the db rather than php the better.

[cooldude832]

I dont' want to do

$item_q[0] = "select * from `items Where UserId= UserID && Status = 1";
$item_q[1] = "select * from `items Where UserId= UserID && Status = 2";
$item_q[2] = "select * from `items Where UserId= UserID && Status = 3";
$item_q[2] = "select * from `items Where UserId= UserID && Status = 4";
...
foreach($item_q as $value){
$temp_r = mysql_query($value) or die(mysql_error());
{
// work items here
}
?>

isn't it best to do 1 big query instead of a million smaller queries? Especially if queries could be empty (i.e user doesn't have an item of status 4 for say)

[cooldude832]

It gets me the same amount of data just less organized so to speak.  Doesn't it load down mysql to say mysql_query() a lot on a page, especially if this page will end up have 10k-100k or more page views per day and each query could be for 100+ rows?

[trq]

select * from items Where UserId = UserID && Status IN(1,2,3,4)

[trq]

Doesn't it load down mysql to say mysql_query() a lot on a page, especially if this page will end up have 10k-100k or more page views per day and each query could be for 100+ rows?

 

There are usually better queries available to get the data you want if your database is designed properly.

 

Looping and sorting through arrays is just going to bog down apache. Your not solving any problems.... just moving it somewhere else.

[ss32]

$sql = "SELECT * From `Items` Where UserID = $userid ORDER BY Status"
$q = mysql_query($sql);

$prevStat = 1;
while ($row = mysql_fetch_assoc($q)) {
  if ($prevStat != $row['Status']) {
    echo $row['Status'];
  }
  //echo the rest here
  $prevStat = $row['Status'];
}

 

 

[cooldude832]

any ideas on limiting the load. 

I need to be able to create different echoing out points on the page sorta an inventory view.

Like status=1 would be a weapon so it needs to show up in the weapon area as the current weapon and then a drop down below it that allows you to pick a new weapon (from your weapons you own i.e status=1) then another area for body armor then another for your general inventory.  It will have more than 3 area (more like 10-15 when done) any new ideas with that info.  I'm not worried about the update query because that will happen more rarely because i'm using JS to allow you to see what an item does before "updating/equipping" and the update only affects 1-2 rows so to speak

 

I think my problem is I'm making it too multideminsonal array.  instead what if i made each field its own array like then i can do it without sorting as much right?

<?php
$q = "SELECT * From `Items` Where UserID = $userid";
$item_r = mysql_query($q) or die(mysql_error());
while($item_row = mysql_fetch_array($item_r)){
foreach ($row as $key => $value){
$.key[] = $value;  //This is the right way for a dynamic variable generation????
} 
}
?>

[ss32]

look what you are doing is GOING to be load-heavy...  if you dont want it to be then get a better server.

 

as far as making it better goes, you could try unioning the queries, ordering the queries, or creating subpages where only weapons/shields/etc are viewable (which would probably decrease the load the most).  what thorpe said is absolutely correct... and having mysql handle most of the data manipulation is a smart idea because it is optimised for it.  it is relatively safe to do a sort in there, or a union if you have to.

[cooldude832]

I'm not saying my server is "slow" "bad" "cheap" i'm saying that I want to write this effectively so that my site runs as best as it can, part of the reason i want to make sure its optimized in coding is so that i can move this to different servers/sell the script and know it will work at the same or better speed.  I really don't think there is a fast way to do this because it is a complex operation, however I am more worried about over loaded sql. 

just based on numbers which is better.

100 queries of 1kb of data each

or

1 query of 100kb of data

or

10 queries of 10kb of data each

 

Sorting/union won't do much for me in this case I can sort by status type, but that doesn't get me very far because a loop is needed to get all data, i'm just looking for the best from a coding point for speed and server reliability.  Also if I get the data off 1 query or 100 queries the information will still have to be converted to php array(s) either way because the echoing is not going to occur in the loop (unless I do each section as its own query i.e max number of queries)

[ss32]

a loop is the only way you can echo the data.  you are going to have to use one.

just make sure that you don't have the loop doing a lot of stuff.  a simple echo/if isnt going to hurt anything.

 

as a rule of thumb, the less queries the better.

and this really isn't a complex operation... its a simple query-sort-echo thing.  that code i gave you should do you well- it leaves the optimized engine to do all of the sorting stuff and merely echoes the information afterwards..  if you really want to be stingy, code all the methods you can think of and benchmark them.

[sKunKbad]

In my opinion, because I have a page where I have 6 SEPERATE mysql_queries, and it is running literally as fast as if they didn't exist, I believe I would have to have something like 200 queries before it really would make a performance difference, and that's only speculation.

 

If you are really going to be selling code, you might want to read a good book or two on mySQL instead of trying to figure it all out with your buddies here on phpfreaks.

 

Also, if programmers had to worry about the computers each of their customers were running their code on, it would be impossible to sell programs. Are you going to write code for somebody still running Windows 3.1? I understand optimizing code, and I do it for all of my programs, but at some point you have done all you can reasonably do, and further optimizing is useless.

[akitchin]

a little more on topic, if you want to narrow down your array to grabbing values by status, use status as a key and grab that array:

 

$q = "SELECT * From `Items` Where UserID = $userid";
$item_r = mysql_query($q) or die(mysql_error());
while($item_row = mysql_fetch_array($item_r)){
$id = $item_row['ItemID'];
unset($item_row['ItemID']);
$status = $item_row['Status'];
unset($item_row['Status']);
$items[$status][$id] = $item_row;
}
}

 

i dropped the ItemID and Status from $item_row since there's no need to repeat them if they're already keys.  furthermore, you don't need to loop to assign the row's values to an array item, since it comes "off-the-shelf" as an array.

[cooldude832]

I think i'll just have to bench mark it out and get back to you with results.  And  to respond to skunkbad I know php, that isn't the problem, its a language, a method, a system what ever you want to call it.  It has a million options to 1 question.  I can think of 30000 ways to get a screen to print hello world for me and that is just a simple thing, I'm trying to get ideas of different ways to run this for benchmarking so I can see which work's best across a gradient of mass for the queries.  I want to make it so it runs good if it is only pulling 10 rows or 10 million rows.  And I don't think you can run php 4+ on a windows 3.1 its not about the client but the server that i'm worried about, I've seen issues where I know how my server works and when it is transfered issues happen.  I know how to normalize tables, index, and follow what is considered standard practice of table and database designs. 

 

Akitchin my only issue with your method is I need that ItemID to use as the value for the form selections so that way I can easily say Update `Items` Set Status = Newstatus Where ItemID = $_POST['equipped'] so to speak, I can probably drop UserID off the query because its pointless and that will save me a bit of time.  I only need the UserID as my needle in the haystack (as php.net refers to searching parameters).  Also if i query it off a single query I can then redistribute the results over new variables then unset($item_row) does that help at all or no?  The resources are already abused for creating it and I only see unsetting as using more resources.  I believe (not sure if its right) was designed for you to reuse common values without having to redeclare a value for them.  That or for internal pointers on looping (a while in while the $j=0 can be replaced with unset($j)) However if it reduces the load, not just free resources it might not be a bad idea because $item_row will be a large array.

[akitchin]

it stores the itemID in the array as the key beneath status.  to grab all the items whose status are 1, use:

 

foreach($items[1] AS $ItemID => $other_info)
{
  echo '<pre>the first matching item with Status 1 is '.$ItemID.' and its info is: '.print_r($other_info, TRUE).'<br />';
}

 

maybe i've missed something as to what you intend to do with the info you're taking from this original query.

[cooldude832]

The ultimate goal of this script is an inventory display with the data drawn off the table to be displayed in different areas.

like on your character you have your equipment show up on a body with the current weapon in your arm then when you switch off a drop down it switches your stats so you can preview it before you submit the results, it also does the same with armor jeweler hats boots gloves and a few others I haven't thought of yet.  Then it also has a general inventory view with all items listed in a nested scrollable frame so to speak of.  So data could be used 1-4 times each time and there are huge blocks of html output between each table data output area which is why i originally though query once sort into arrays and display in each block with loops.  But then people are saying use a query in each block for data and I thought that would kill my mysql especialyl if i'm querying info I already have.

[akitchin]

you're going to have to run the MySQL query/ies at some point, and store then loop through the data, or loop through it and display it in "real-time" so to speak.  regardless of what you do, MySQL will have to pull the exact same amount of information, so the bottleneck will come down to the PHP you use to store the data and display it.

 

what data structure are you looking for in the end?  separate arrays for each item status?

[cooldude832]

this one i posted earyl requires the least php i think

<?php
$q = "SELECT * From `Items` Where UserID = $userid";
$item_r = mysql_query($q) or die(mysql_error());
while($item_row = mysql_fetch_array($item_r)){
foreach ($row as $key => $value){
$.key[] = $value;  //This is the right way for a dynamic variable generation????
} 
}
?>

because I can simply use the indexial keys to find stuff very easily and if i order by status i can know the starts of the status by checking the status variable aganist its self. and know where each new status type starts.  I figure going multideminsonial would create a bottle next because i would have to create search algothrims, instead I search the array $status() and get where status 1 start 2 3 etc etc and then in each group just say $i=start[$statusnum] while ($i<$start[$statusnum+1]){ Show data

[akitchin]

that's incorrect for dynamic variable generation:

 

<?php
$q = "SELECT * From `Items` Where UserID = $userid";
$item_r = mysql_query($q) or die(mysql_error());
while($item_row = mysql_fetch_array($item_r)){
foreach ($row as $key => $value){
${$key}[] = $value;  //This is the right way for a dynamic variable generation????
} 
}
?>

 

not sure if that will work with the array notation.  nonetheless, you're still ignoring the PHP you'll have to use to parse those arrays and display them properly.

[cooldude832]

That isn't going to be difficult i said all i have to do is find the cuts in the array $status (dynamically made and thanks for fix on notation) and then i'm all set.

[roopurt18]

I want to make it so it runs good if it is only pulling 10 rows or 10 million rows.

 

Once you start pulling in the tens of thousands and higher, no matter how optimized you make things, its always going to take a while to load in a browser.  This is why you set up tables with proper indexes and use pagination where appropriate.

 

If you're expecting to pull 10 million rows, process them, and spit them out to the user before they age a bit, you're on some pretty strong stuff.