[SOLVED] using arrays with mysql query results

[Optimo]

Hello, im currently in the process of making a piece of code that will firstly take all the data from a table in mysql and then assign it to an array. This data will consist of several different rows of data each with different infomation for each field in the table. i want to be able to use an array in a loop so that depending on which number of the loop is on will change the infomation that is assigned to a certain array. below is an example of what i want to do:

 

    $query = mysql_query("SELECT * FROM ___________");
    $ship_values = mysql_fetch_array($query);

    for($x = 1; $x < 17; $x++)
    {
        $unit_data = $ship_values[$x];
        
        $unit_name = $unit_data[name];
        $unit_crew = $unit_data[crew];
        $unit_type = $unit_data[type];
        $unit_money = $unit_data[money];
        $unit_deterium = $unit_data[deterium];
        
        echo"
        <br>
        X = $x
        <br>
        Unit Name: $unit_name<br>
        Unit Deterium: $unit_deterium<br>
        Unit Type: $unit_type<br>
        Unit Money: $unit_money<br>
        Unit Crew: $unit_crew<br><br>
        <br>
        <br>
        <br>
        ";
    }

the problem is for example the 1st time the for loop runs i want the name of the 1st result from the query to be displayed but it is instead displaying the following (3 runs of loop):

 

X = 1

Unit Name: L

Unit Deterium: L

Unit Type: L

Unit Money: L

Unit Crew:

 

X = 2

Unit Name: f

Unit Deterium: f

Unit Type: f

Unit Money: f

Unit Crew:

 

X = 3

Unit Name: 9

Unit Deterium: 9

Unit Type: 9

Unit Money: 9

Unit Crew:

where the actual data in the database table for the 1st row is:

name: Lance

type: fighter

networth: 9

 

so basicly the loop is going through the value of each field in order and getting the data of the 1st row only but i want each loop to go down the different rows and have the variables set for each field so i can use them in other places.

 

I know theirs an easier way to do this with:

 

$query = mysql_query("SELECT * FROM XXXXX");

while ($row = mysql_fetch_array($query))
{
etc....
}

but i want to use the variables in many different places throughout different includes and also display them as part of tables that in themselves have statements that decide if they are shown.

 

can anyone point me in the right direction im fairly new to arrays and cant work out what is going wrong.

[GingerRobot]

Well, i *think* from your question what you're after is:

 

<?php
$sql = mysql_query("SELECT * FROM `yourtable`") or die(mysql_error());
$x = 0;
$unit_names = array();
$unit_crew = array();
$unit_type = array();
$unit_money = array();
$unit_deterium = array();
while($row = mysql_fetch_assoc($sql)){
$unit_names[] = $row['name'];
$unit_crew[] = $row['crew'];
$unit_type[] = $row['type'];
$unit_money[] = $row['money'];
$unit_deterium[] = $row['deterium'];
echo '<br/>x='.$x'<br />Unit Name: '.$row['name'].'<br />Unit Deterium: '.$row['deterium'].'<br />Unit type:'.$row['type'].'<br />Unit Money: '.$row['money'].'<br />Unit Crew:'.$row['crew'].'<br /><br />';
$x++;
}
?>

 

You'd then use the relevant array and key to get the information you need further on in the script. I would suggest, however, that you use the id field (that i hope you have) as the key for the arrays. It would make things easier to keep track of.

[Optimo]

well im not sure that will solve my problem basicly what i would like to do is be able to use some code at any point in any script as long as i have the script that does

 

$query = mysql_query("SELECT * FROM ___________");
    $ship_values = mysql_fetch_array($query);

 

included in the page.

 

so either in a loop that will set the value of $x every time it loops around. or possibly manually set it in a certain part of a page for example:

 

//Lance
$x = 1;
$ship_data_array = $ship_values[$x];
$max_var = "max"."$x";
echo"
	<tr>
";
if($user[diable_images]=="No")
{
	$img = "<img src=../../images/ships/$ship_data_array[name].jpg>";
	echo"
		<td class=second>$img</td>
	";
} 
echo"
		<td class=second>$ship_data_array[name]</td>
		<td class=second>$ship_data_array[offense]</td>
		<td class=second>$ship_data_array[defense]</td>
		<td class=second>$picmoney"; echo number_format(ceil($ship_data_array[money] * $build_cost_mod)); echo"</td>
		<td class=second>$picdeterium"; echo number_format(ceil($ship_data_array[deterium] * $build_cost_mod)); echo"</td>
		<td class=second>$piccrew $ship_data_array[crew]</td>
		<td class=second></td>
		<td class=second></td>
		<td class=second></td>
		<td class=second><input type=number name=u1 size=5 value=0></td>
	</tr>
";

 

 

but also their might be a case where a loop statement calculates a total cost of building every type of unit so it needs to know how much each unit costs to build and then add it to the total which is then used further in the script.

 

i am going to be displaying certain number of HTML tables and doing certain numbers of loops based on theirs only 17 types of units, if i was to add more i would then add the new code to the page, so just doing something based on every result in the table is not what i want.

 

edit:

 

and yes each record of the table has a unique id

[GingerRobot]

Well do it like this then, and see how you would use it:

 

<?php
$sql = mysql_query("SELECT * FROM `yourtable`") or die(mysql_error());
$unit_data = array();
while($row = mysql_fetch_assoc($sql)){
	$id = $row['id'];
$unit_data[$id]['name'] = $row['name'];
$unit_data[$id]['crew'] = $row['crew'];
$unit_data[$id]['type'] = $row['type'];
$unit_data[$id]['money'] = $row['money'];
$unit_data[$id]['deterium'] = $row['deterium'];
}
print_r($unit_data[1]);//print out Lance's data
foreach($unit_data[2] as $v){
//cycle through the second set of data
}
$total = 0;
foreach($unit_data as $v){
$total += $v['money'];
}
echo 'Total cost: '.$total;//show the total of all the money fields
?>

[Optimo]

thanks for the help, i think that is a possible solution im still working on it now im yet to get it working however

 

but what im wondering is maybe i just need to simplify my question...

 

is their no way to transfer the results of an sql statement (with multiple results with multiple fields in each) into an variable array (that you can do with mysql_fetch_array) and then pass a certain result number and all the fields attached to it into a new array.

 

so say the original array was like this (example of what the code from the DB could look like):

 

array(
"1" => array
	(
		"id" = > "1",
		"name" => "lance", 
		"deterium" => "10000", 
		"money" => "50000"), 
"2" => array 
	(
		"id" = > "2",			
		"name" => "freedom", 
		"deterium" => "99999", 
		"money" => "88888"
	)
);

 

so an example code would be:

 

$query = mysql_query("SELECT * FROM ________");
$main_array  = mysql_fetch_array($query);

 

how could i then pass the 2nd result into a new array so that i could then use the following variable call to get the data within it:

 

$sub_array[name]

 

as we have discovered simply:

 

$sub_array = $main_array[2];

 

doesn't seem to work

 

[teng84]

while($row = mysql_fetch_array($sql)){

$array[] = $row;

}

print_r($array[1]);

print_r($array[2]);

print_r($array[2]);

[Optimo]

while($row = mysql_fetch_array($sql)){

$array[] = $row;

}

print_r($array[1]);

print_r($array[2]);

print_r($array[2]);

 

thanks, that seems to be what i want appreciate all the help. appologies if i was a bit slow in picking up what you guys meant

[teng84]

mark your thread as solved if it is !