Inserting database values into an array.

[dkin]

I have my item database almost complete, but there are a few things I must tweak.

This is what I need.

I have many attributes for each item

AC
AR
PR
FR
Hp
Pow
etc
etc

I am wanting to display only the attributes which have values and I beleive an array would be the easiest way to do this.

So my first question.

Can I put mysql database values into an array?

I tried and it did not work. I used this as my value

$row["AC"];

If someone could correctly insert this into an array and show me I would be very thankful.

Second is how would I go about only showing the attribute name if there is a value in it?

[Spontan]

try this.

$query = SELECT itemname FROM itemdb;
$result = @mysql_query($query);
if($result)
{
$row = mysql_fetch_row($result);
}

this will put the whole row across on the db into an array called $row[].

So whatever is field is first in the database will be $row[0] and so on and so forth....From there you can check to see if $row[] array is blank in certain spots and you can display only what you want.

Not sure if a better way..other can suggest...

[dkin]

I have 21 rows that I would like to put in this array. Will I be able to use 21 rows with this code?

Sorry I am a newb when it comes to php.

[andrewB]

You can do this

$query = SELECT itemname FROM itemdb;
$result = @mysql_query($query);
if($result)
{
$row[] = mysql_fetch_row($result);
}

so I just changed $row to $row[]

so you can loop over your records like so
for ($i=0; $i < count($row); $i++)
print_r($row[$i]);

so every element in $row is 1 record array

[dkin]

This is what I have put on my page but I get a blank page why?

<?php
$db = mysql_connect("localhost", "*****", "*****");
mysql_select_db("*****",$db);
$query = mysql_query("SELECT Item_Name FROM nuke_item_database");
$result = @mysql_query($query);
if($result)
{
$row[] = mysql_fetch_row($result);
}

for ($i=0; $i < count($row); $i++)
print_r($row[$i]);

echo $row[0];
?>

[jatar_k]

the connect stuff is fine

here you are doing a mysql_query twice and don't use the @ symbol for development since it supresses errors

$query = mysql_query("SELECT Item_Name FROM nuke_item_database");
$result = @mysql_query($query);

this only gets a single row

if($result)
{
$row[] = mysql_fetch_row($result);
}

a while loop will work just fine here instead of for

for ($i=0; $i < count($row); $i++)
print_r($row[$i]);

echo $row[0];

I am not sure why there is a print_r and a single echo after but let's clean it up a bit

<?php 
$db = mysql_connect("localhost", "*****", "*****"); 
mysql_select_db("*****",$db); 
$query = "SELECT Item_Name FROM nuke_item_database"; 
$result = mysql_query($query) or die ('<p>' . mysql_error()); 
while ($row = mysql_fetch_array($result)) { 
 echo '<br>',$row['Item_Name']; 
} 
?>

I prefer to use mysql_fetch_array and I always assign my staement to a variable and then pass that to mysql_query. It helps if I need to display the actual query when it is constructed dynamically. I also added an 'or die' to the actual query to return the mysql error if there is one. It should be removed before it is put live as errors should be handled and not just kill the script.

see if that works.

[dkin]

That will give me a list of my items like this.

Red Widgets
Blue Widgets
etc
etc

What I am trying to do is have a script that detects if there is a value in a row. these rows are not required so I do not want to put

$row["myrow"]
<br>
$row["myrow2"]
etc
etc

because I will have alot of blank lines.

So I need to first see if there is a value in "$row["myrow"]" and if there is I need to match it with the corresponding row name, if there is not I want to move on.

Did I explain that ok?

[jatar_k]

just put an if in there then

<?php 
$db = mysql_connect("localhost", "*****", "*****"); 
mysql_select_db("*****",$db); 
$query = "SELECT Item_Name FROM nuke_item_database"; 
$result = mysql_query($query) or die ('<p>' . mysql_error()); 
while ($row = mysql_fetch_array($result)) { 
 if (!empty($row['Item_Name'])) echo '<br>',$row['Item_Name']; 
} 
?>

[Birdman]

It seems to me that if you only want to grab rows with a value, then you can very easily do that within your SQL statement.

SELECT Item_Name FROM nuke_item_database [b]WHERE Item_Name <> ''[/b]

Birdman

Added: If that doesn't work, the you may need to use:

SELECT Item_Name FROM nuke_item_database [b]WHERE Item_Name <> '' AND Item_Name IS NOT NULL[/b]

[edited by: Birdman at 2:19 am (utc) on June 22, 2004]

[jatar_k]

good point Birdman, fix the query not program for one that isn't quite right.

[dkin]

Now I am no good at php so I do not really understand what you have shown, but, I will try to explain this well, in detail and you tell me if that will work.

I have roughly 18 rows I would like to use this for, chances are most of the time less than 4 will actually show (depending on the items attributes.)

Now I have rows named"AC, Hp, PR, etc, etc"

If Hp has a value I would like to display Hp: Value (Hp: will not be coming from the database I will have to add it in the script) If PR does not have a value I would like that row discarded for the particular item, no space, break or anything.

So for all rows that do have values I would like the php script to attatch the appropriate prefix, if it has no value I would like it discarded and the script to move on to the next attribute.

Will that code do this?

[Birdman]

I'm not understanding exactly what you need here. Does your database table have a field for each of these attributes? If so, the you need to change your sql. Right now you are only grabbing Item_Name from the db when you probably need to get more fields.

I think we need to know how your database table is laid out to better understand.

[Birdman]

If you do have fields in your db named AC, AR, etc then you need to add the name of each field to the SELECT part of the sql.

$query = "SELECT Item_Name, AR, AC, Pr etc... FROM nuke_item_database";

You could then do something like this:

$result = mysql_query($query) or die ('<p>' . mysql_error());

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

print $row[Item_Name] . "<br />";

foreach ( $row as $key => $val ) {

if (!empty($val) && $key!= "Item_Name" ) {

print $key . " : " . $val . "<br />";

}

}

}

[dkin]

Yes I have about 40 fields in my db. I do have a seperate field for each attribute.

What exactly would this last script do?

[dkin]

Also I would should inform everyone that I will only need about 18 values in this script and I woul like it to display with every item that is shown. Not on the item page where everything is entered individually but I have an if loop which displays all of my items. This is the layout.

Item Name ¦ Item Level ¦ Item Classes ¦ Item Races¦ Attributes

Beneath eac I would like the corresponding row ie row["Item_Name"]

I do not believe that entering 18 row[""]'s would be very suitable, for one WAY to many row[""]'s and two I beleive that there would be far to many spaces.

ie

if I had this

row["AC"] row["Damage"] row["AR"] row["PR"] row["Hp"] row["FR"]

If there was no value for "AR" would the script just skip over it and move onto the next value or would it display a large space where the value is supposed to be.

Also this would not be very logical for me seeing as I need to display the value with the corresponding attribute.

So if there is a value of 26 in the row AC and nothing in AR I would like AC to display AC: 26 and skip over AR to the next attribute with a value.

I am very bad at explaining but I am giving it my best shot. Thank you everybody for responding.

[dkin]

anyone?

[coopster]

I believe jatar_k and Birdman have shown a couple of examples of how to accomplish your goal, if we are understanding you correctly. The loop will process your retrieved recordset and the PHP empty() [php.net] function will see if there is a value in any given column. Combining the loop with the "if empty" condition seems to be what you are looking for. If not, you may have to show us an example of your data as it is being retrieved, and what you would like it to end up looking like when you display it.

[dkin]

This is what my db looks like go to this url for a screenshot.
<snip>

For rows which have a value I would like to disaplay the value and something like the name of the row to indicate what the value is for. If there is no value in the row I would like the script to move onto the next row and check for its value.

Am I doing any better lol.

[edited by: jatar_k at 2:07 am (utc) on June 23, 2004]
[edit reason] sry no urls thanks, you can post a describe table though [/edit]

[Birdman]

What exactly would this last script do?

You haven't tried it yet?

The best way to learn is to get it there and try things.

Try running the code in message 13 and then post your results. I'm pretty sure it will work out for you but you have to try it to find out.

It's better to teach someone how to fish, than to just give them a fish.

I'm eager to hear the results, let us know.

Birdman

[dkin]

This is the results of that script.

1 : 11
AR : 11
2 : 224
AC : 224

This is the code that I used.

<?php $db = mysql_connect("localhost", "*****", "*****");
mysql_select_db("*****",$db);
$query = "SELECT Item_Name, AR, AC, Pr FROM nuke_item_database";
$result = mysql_query($query) or die ('<p>' . mysql_error());

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

foreach ( $row as $key => $val ) {

if (!empty($val) && $key!= "Item_Name" ) {

print $key . " : " . $val . "<br />";

}

}

}

?>

I do not know why it has the numbers 1 and 2.

[Birdman]

Apparently, the $row array has numeric keys as well as the field names. I'm not sure it's the best way but if you change this line, it should skip the numbered keys.

if (!empty($val) && $key!= "Item_Name" && !is_numeric($key) ) {

Birdman

[coopster]

The 1 and 2 are the numeric indices. Simply use mysql_fetch_assoc() [php.net] rather than mysql_fetch_array() if you don't need the numeric indices.

[dkin]

Ok that worked perfectly, now I have to insert all the rows.

But how do I get the results to correspond with a particular ID in a table?

[Birdman]

Good call coopster! Learn something every day :)

But how do I get the results to correspond with a particular ID in a table?

By ID, do you mean Item_Name?

If so, just go back and look at the script in msg#13 and add the line that you removed.

[dkin]

I do not understand.

I have all the output from my db in a table.
I would like the values for the particular item to be in the same <tr> as the item it corresponds to. Than cycle down to the next row with the next item name.

I know everyone has helped me a great deal, and we have made quite a bit of progress but this is so hard and if anyone can even suggest an easier way, please I would like to hear it.

Thank you for your assistance.

[coopster]

This is where you apply the markup/formatting during your loop. Where you were originally formatting breaks, change your structure to accommodate a table. Something like...

while ($row = mysql_fetch_array($result)) { 
 print '<tr>'; 
 foreach ( $row as $key => $val ) { 
  print '<td>'; 
  if (!empty($val) && $key != "Item_Name" ) { 
   print $val; 
  } 
  print '</td>'; 
 } 
 print '</tr>'; 
}

[dkin]

That looks like it will work.
I will try it and post the results.

Thank you

[dkin]

This was the result of that.

111122422417717714771171477117

This is the source code

<html>

<head>
<title></title>
</head>

<body>
<tr><td></td><td></td><td>11</td><td>11</td><td>224</td>
<td>224</td><td></td><td></td></tr><tr><td></td><td></td>
<td>177</td><td>177</td><td>1477117</td><td>1477117</td>
<td></td><td></td></tr></body>

</html>

[edited by: jatar_k at 6:13 pm (utc) on June 23, 2004]
[edit reason] fixed sidescroll [/edit]

[dkin]

Also all items should be in the same <td> with one or maybe 2 spaces in between them.

[dkin]

I've altered the code slightly and this is now the source code.

<html>

<head>
<title></title>
</head>

<body>
<td>112241771477117</td></body>

</html>

Which is what I want but now how do I get the names of the values in the code with it?