[SOLVED] Simplify This Query

[Simsonite]

Hi i have created this query however it is draining my server to much and would like to know how to simplify it .

 

The script is at www.wlfw.co.cc/phpfreaks.txt

[xtopolis]

$qgroup = "SELECT name FROM ".TBL_USER_GROUPS." WHERE id IN('1','2','3','4','5','6','7','8')";

$result = mysql_query($qgroup, $this->connection);

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$group = $row['name'];
}

Perhaps?

[Simsonite]

hmm sounds good and how could i get a separate array for each row?

[Mchl]

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
   $group[$row['id']] = $row['name'];
}

[Simsonite]

I did what you said and it is coming up with the error Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home.....

 

What have i done wrong?

[xtopolis]

What does $row['name'] return?  Or what do you mean separate array per row?  Perhaps there's an easier way of getting the result/formatting you want?  Explain.

[Simsonite]

Basically i want to store a number of names for different ranks and then have each of them with a rank of 1 to 8.

 

Then i want a script to take this data from the database and asign each name a variable.

 

However when i try to have a different query for each id it caused the script to take up to much data and crash.

 

I hope this helps

[xtopolis]

Maybe something like this will work:

$qgroup = "SELECT CONCAT_WS(',',name) FROM ".TBL_USER_GROUPS." WHERE id IN('1','2','3','4','5','6','7','8') GROUP BY id";

 

That should return a data set of 8 rows with a column name that looks like:

name

-------

names,in,group,one,comma,separated

names,in,group,two,comma,separated

 

From there you can use split in PHP to do stuff with it.

 

 

It's hard to tell without you posting your structure and some data via a mysql dump..

 

[Simsonite]

Thanks and sorry for being such a beginner

 

[xtopolis]

Did that work?  I wasn't trying to be condescending.. it's just hard to speculate problems from vague descriptions...  if you need more help, let us know, otherwise, click topic solved.

[Simsonite]

i will try it in a sec

[Simsonite]

hmm it is still coming up with the error

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home//////public_html/login/include/usergroup.php on line 23

 

Here is the code if you need any more info just ask

 

<?php

$database['dbserver']="//";
$database['dbuser']="//";
$database['dbname']="//";
$database['dbpass']="//";

$con = mysql_connect($database['dbserver'],$database['dbuser'],$database['dbpass']);

mysql_select_db($database['dbname'], $con);


/*************************Queries*****************************/

$qgroup = "SELECT CONCAT_WS(',',name) FROM ".TBL_USER_GROUPS." WHERE id IN('1','2','3','4','5','6','7','8') GROUP BY id";

/*************************Results*****************************/

$result = mysql_query($qgroup1, $con);

/*************************Variables****************************/

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$array = split(',', "$row");
}

echo $array;

?>

[fenway]

Let's see... you're running a query called $ggroup1, but your query string is in $ggroup.  Catching mysql_error() will help in the future.

[Simsonite]

Ok that is fixed thanks for that.

 

However how do i split the query i have tried but i just returns "Array"

[Mchl]

$row is already an array. Split $row[0]

[Simsonite]

HI this code still returns "array"

 

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$array = split(',', "$row[0]");
echo $array;
}

[Mchl]

Of course it does. $array is an array after all. If you want to display its fields use

 

for($ = 0; $i < count($array); $i++) {
  echo $array[$i];
}

 

 

[Simsonite]

It comes up with an error for the script you gave me

 

Something is wrong with the $ = 0

 

Parse error: syntax error, unexpected '=', expecting T_VARIABLE or '$' in /home/____/public_html/login/include/usergroup.php on line 27

[xtopolis]

for($i = 0....

 

he forgot the i

[Simsonite]

I see =)

 

But everybody you have got to realise that i am learning php so please dont get frustrated with me

[Mchl]

 

But everybody you have got to realise that i am learning php

 

Like all of us... or the majority at least

[Simsonite]

Lol but i have only just started and i am trying to find out how the syntax works