while loop

[Zester]

Hi Everyone,

I know how to do a loop for each row in the DB.
What I need to do is make a loop for each column of a DB table, if I do not know how many column there will be.
so that it run the code for every column in the table like I did with the rows.

Code: Select all

$sel="select * from lqlotl.inventory where lqlotl.inventory.charid='$charid' ";
 $result=mysql_query($sel) or die("could not select Race");
 $num_rows = mysql_num_rows($result);
while ($result2 = mysql_fetch_array($result)) {
some code
$name =$result2[name];
}

Another thing that is stumping me is how to auto create a table from the rows of another table.

Lets say I have Table of skills called skill, each row holds the info of one skill, the column are id name type and so on.
what I need to do is to create a charskills table on the fly or at start up, from the skills table.
So charskill table will have the column id then a number of column for each rows of the skills table, so that the name of each skill is the heard of each column.

I have no idea on how to do this at all if I do not know the amount of rows before hand?

would love some help with this.

cheers

[Jackolantern]

There are ways to reflect on a MySQL database to find out the number of columns and other similar information, but honestly, I would just store the query results in an enumerated array (an array that uses just a number index to get each column worth of data). Then you can just set up a WHILE or FOR loop and go through them all. You can get an enumerated array from your result set one row at a time with:

Code: Select all

mysqli_fetch_row();

or

Code: Select all

mysql_fetch_row();

...depending on if you are using mysql or mysqli extensions. Here is the mysql function's entry in the PHP manual, which has several examples of its usage.

As far as the second issue, I would suggest just making queries of the tables you want to base the new table on, and then use the query results to build a CREATE TABLE query and then run that. Likely what you would do is start off a string for the CREATE TABLE query with the required beginning text, and then query for the existing base table, and then inside of a WHILE or FOR loop, loop through the results and continue building up the CREATE TABLE query with the += operator as you hit each record you need to add. Then you could add any other SQL info at the end of the CREATE TABLE query and then run it.

Or you could just make the table, query for the base table info, and then run a WHILE or FOR loop and INSERT each record as needed. This would likely incur a bit more overhead due to all of the separate queries, but unless we are talking thousands upon thousands of records, it is unlikely to matter.

[Zoo]

ok, what i need to do if i wanna what data put to another table?
Like now i have problem, for exemple:
I try do: Buy Shield from armor shop, but i dont wanna put it together with another bodey armor, so i wanna put it on ma second hand so.. I must use
first buy it -> SELECT everything from "location" called shield -> put in secondhand table?
I try do it (yeee, im nooooob) but.. I still learning it.

Code: Select all

$itembought = "INSERT into playerarmor(pid, name, rating, level, randid,price,class,location) VALUES ('$playerid','$name','$rating','$wlevel','$randid','$newprice','$class','$location')";
mysql_query($itembought) or die("could not insert weapon into backpack");

$shield = mysql_query("SELECT * FROM playerarmor WHERE location='shield'");
while( mysql_fetch_array($shield))
  {
  echo $shield="INSERT into secondhand(pid, name, rating, level, randid,price,class,location) VALUES ('$playerid','$name','$rating','$wlevel','$randid','$newprice','$class','$location')";
mysql_query($shield) or die("could not insert weapon into backpack");

  }

$updateplayer="update players set gold=gold-'$newprice' where name='$player'";
  mysql_query($updateplayer) or die("Could not update player");

Grr.. its so confusing..

[alexrules01]

Your first error is this line

Code: Select all

$shield = mysql_query("SELECT * FROM playerarmor WHERE location='shield'");

I assume it should be

Code: Select all

$shield = mysql_query("SELECT * FROM playerarmor WHERE location='$shield'");

Otherwise your searching the database for all locations that are named 'shield'

The second error I can see is your trying to feth the array before actually quering the database
This should work better

Code: Select all

$shield = mysql_query("SELECT * FROM playerarmor WHERE location='$shield'");
$shiled_query = mysql_query($shield) or die("could not insert weapon into backpack");
while( mysql_fetch_array($shield_query))
  {
  $shield_insert="INSERT into secondhand(pid, name, rating, level, randid,price,class,location) VALUES ('$playerid','$name','$rating','$wlevel','$randid','$newprice','$class','$location')";
  }

Using the while statement could insert more then one 'shield' in the database. If you only want to insert one, I suggest use an IF statement instead. But I'm not sure what your after.

Hope they helped, and were correct lol

[Zoo]

Hey again,

i think

Code: Select all

    $shield = mysql_query("SELECT * FROM playerarmor WHERE location='shield'");

its alright, cuz i wanna take all items from column "location" whos calls shield and then put into secondhand table
I tryed urs code, but got error Warning: mysql_query() expects parameter 1 to be string, resource given in C:\wamp\www\test\armorshop.php on line 89
and cant connect to database..

Code: Select all

<?php
include_once 'connect.php';
 session_start();
   include_once 'logo.php';
   ?>
     <link href="style.css" rel="stylesheet" type="text/css" />
<?php
if (isset($_SESSION['player']))
{
  $player=$_SESSION['player'];
}
else
{
  echo "Not Logged in <br><br> <A href='login.php'>Login</a>";
  exit;
}
$playerinfo="SELECT * from players where name='$player'";
$playerinfo2=mysql_query($playerinfo) or die("could not get player stats!");
$playerinfo3=mysql_fetch_array($playerinfo2);
include_once 'statpanel.php';
?>
</div>
<div id="table">
 <?php
 $bypass = 0;
$counter = 0;
$class = $playerinfo3['pclass'];
$level = $playerinfo3['level'];
$pllocation=$playerinfo3['location'];

 $inninfo="SELECT * from locations where location='$pllocation' AND type='armorshop'";
$inninfo2=mysql_query($inninfo) or die("could not get player stats!");
$inninfo3=mysql_fetch_array($inninfo2);
 $shoplevel = $inninfo3['level'];
 if (isset($_GET['bought']))
 {
 $bypass = 1;
 
  $id=$_GET['id'];


$playerid = $playerinfo3['id'];
$class = $playerinfo3['pclass'];
$level = $playerinfo3['level'];

$iteminfo="SELECT * from armor where id='$id'";
$iteminfo2=mysql_query($iteminfo) or die("could not get item stats!");
$iteminfo3=mysql_fetch_array($iteminfo2);

if ($playerinfo3['gold'] < $iteminfo3['price'])
{
 echo "You do not have enough Gold for this purchase!";
  echo "<center><a href='index.php'>Go Back</center>";
  exit;
}


if ($playerinfo3['pclass'] != $iteminfo3['class'] && $iteminfo3['class'] != "any")
{
 echo "You are not the right class for this item!";
  echo "<center><a href='index.php'>Go Back</center>";
  exit;
}

if ($playerinfo3['level'] < $iteminfo3['level'])
{
 echo "You are not high enough level to use this item!";
  echo "<center><a href='index.php'>Go Back</center>";
  exit;
}



$name = $iteminfo3['name'];
$rating = $iteminfo3['rating'];
$wlevel = $iteminfo3['level'];
$location = $iteminfo3['location'];
$randid = rand(1000,999999999);

$percentage = $iteminfo3['price'] / 100;
	  $price = $iteminfo3['price'] * $percentage;
      $newprice = $price + $iteminfo3['price'];
	  $newprice = (int)$newprice;

$itembought = "INSERT into playerarmor(pid, name, rating, level, randid,price,class,location) VALUES ('$playerid','$name','$rating','$wlevel','$randid','$newprice','$class','$location')";
mysql_query($itembought) or die("could not insert weapon into backpack");

$shield = mysql_query("SELECT * FROM playerarmor WHERE location='shield'");
$shiled_query = mysql_query($shield) or die("could not insert weapon into backpack");
while( mysql_fetch_array($shield_query))
      {
      $shield_query="INSERT into secondhand(pid, name, rating, level, randid,price,class,location) VALUES ('$playerid','$name','$rating','$wlevel','$randid','$newprice','$class','$location')";
      }

$updateplayer="update players set gold=gold-'$newprice' where name='$player'";
  mysql_query($updateplayer) or die("Could not update player");

echo $name . " Purchased<br><br>";
echo $inninfo3['exitgreet'] . "<br><br>";
 echo "<a href='index.php'>Go Back</center>";
}



 if ($bypass != 1)
 {
 echo "<center>" . $inninfo3['greeting'] . "<br>";


 echo "<small>";
      print "<center>";
      print "<table border='0' width='70%' cellspacing='20'>";
      print "<tr><td width='25%' valign='top'>";
      print "</td>";
      print "<td valign='top' width='75%'>";
      $invinfo="SELECT * from armor where class = '$class' AND level <= '$level' AND level <= '$shoplevel' || class='any'";
      $invinfo2=mysql_query($invinfo) or die("could not select anything from the store.");
      print "<table border='1' bordercolor='white' bgcolor='#ffffff'>";
      print "<tr><td>Name<font color='ffffff'>________________</td><td>Rating<font color='ffffff'>______</td><td>Location<font color='ffffff'>______</td><td>Price<font color='ffffff'>______</td><td><font color='ffffff'>________________</td></tr>";
      while($invinfo3=mysql_fetch_array($invinfo2))
      {
      $counter = 1;
	  $percentage = $inninfo3['price'] / 100;
	  $price = $invinfo3['price'] * $percentage;
      $newprice = $price + $invinfo3['price'];
	  $newprice = (int)$newprice;
	  print "<tr><td>$invinfo3[name]</td><td>$invinfo3[rating]</td><td>$invinfo3[location]</td><td>$newprice</td><td><A href='armorshop.php?id=$invinfo3[id]&bought=1'>Buy Armor</td></tr>";

      }

      print "</table>";
      print "</td></tr></table>";
      print "</center>";
      echo "</small>";

      if ($counter == 0)
      {
            echo "<center>There is nothing in the store at this time.<br>";
            echo "<a href='index.php'>Go Back</center>";
            exit;
      }
  echo "<center><a href='index.php'>NeverMind</center></a>";
  }
  ?>
  </div>

i put all armorshop.php code here, maybe somewhere in another row isint good..

[Foofighter]

hi,
yeah u made a few mistakes.

Code: Select all

//thiss part seems to be right

    $itembought = "INSERT into playerarmor(pid, name, rating, level, randid,price,class,location) VALUES ('$playerid','$name','$rating','$wlevel','$randid','$newprice','$class','$location')";
    mysql_query($itembought) or die("could not insert weapon into backpack");
//until here
//-------------------------------------------------------------------------------------------------------------------------------
//here is your first mistake as mentioned by alex but it also makes no sense to me, i would do it as follows:
//i changed the location part with pid cause it makes no sense and its good to work here with the userids or session user ids
//either wouldnt do a while here, for what use?

    $ITEM = mysql_query("SELECT * FROM playerarmor WHERE pid ='$userid'");
    $weapons = mysql_fetch_assoc($ITEM);

//why u doing an echo here? echo means you want to display something.
//why u doing this here? you can put the whole stuff in the backpack at the buying part or do you wanna equip items with this step then do the following

$equipweapon = "INSERT INTO secondhand(randid) VALUES ('$randid')";

//it is just not necessary to have the same data in 2 DB tables
//here is also a mistake we cant get the value for gold this way and yo we cant substract it with the price u miss a querry for your player data like following
$stats_get = mysql_query("SELECT * FROM `players` WHERE `pid`='$userid'") or die(mysql_error());
$players = mysql_fetch_assoc($stats_get);
//in this querry u get your needed players data out of the DB
//just a tip but write the mysql commands in capital letters makes it a bit easier to read

    $updateplayer="UPDATE players SET gold ='$players['gold'] - $newprice where pid ='$userid'";
      mysql_query($updateplayer) or die("Could not update player");

//just to summarize, looks pretty got for your first bit of code but watch the syntax a little more :)
//hope i made no mistake^^

 

hope its right, code not testet

regards

[Foofighter]

Hey again,

i think

Code: Select all

    $shield = mysql_query("SELECT * FROM playerarmor WHERE location='shield'");

its alright, cuz i wanna take all items from column "location" whos calls shield and then put into secondhand table
I tryed urs code, but got error Warning: mysql_query() expects parameter 1 to be string, resource given in C:\wamp\www\test\armorshop.php on line 89
and cant connect to database..

Code: Select all

<?php
include_once 'connect.php';
 session_start();
   include_once 'logo.php';
   ?>
     <link href="style.css" rel="stylesheet" type="text/css" />
<?php
if (isset($_SESSION['player']))
{
  $player=$_SESSION['player'];
}
else
{
  echo "Not Logged in <br><br> <A href='login.php'>Login</a>";
  exit;
}
$playerinfo="SELECT * from players where name='$player'";
$playerinfo2=mysql_query($playerinfo) or die("could not get player stats!");
$playerinfo3=mysql_fetch_array($playerinfo2);
include_once 'statpanel.php';
?>
</div>
<div id="table">
 <?php
 $bypass = 0;
$counter = 0;
$class = $playerinfo3['pclass'];
$level = $playerinfo3['level'];
$pllocation=$playerinfo3['location'];

 $inninfo="SELECT * from locations where location='$pllocation' AND type='armorshop'";
$inninfo2=mysql_query($inninfo) or die("could not get player stats!");
$inninfo3=mysql_fetch_array($inninfo2);
 $shoplevel = $inninfo3['level'];
 if (isset($_GET['bought']))
 {
 $bypass = 1;
 
  $id=$_GET['id'];


$playerid = $playerinfo3['id'];
$class = $playerinfo3['pclass'];
$level = $playerinfo3['level'];

$iteminfo="SELECT * from armor where id='$id'";
$iteminfo2=mysql_query($iteminfo) or die("could not get item stats!");
$iteminfo3=mysql_fetch_array($iteminfo2);

if ($playerinfo3['gold'] < $iteminfo3['price'])
{
 echo "You do not have enough Gold for this purchase!";
  echo "<center><a href='index.php'>Go Back</center>";
  exit;
}


if ($playerinfo3['pclass'] != $iteminfo3['class'] && $iteminfo3['class'] != "any")
{
 echo "You are not the right class for this item!";
  echo "<center><a href='index.php'>Go Back</center>";
  exit;
}

if ($playerinfo3['level'] < $iteminfo3['level'])
{
 echo "You are not high enough level to use this item!";
  echo "<center><a href='index.php'>Go Back</center>";
  exit;
}



$name = $iteminfo3['name'];
$rating = $iteminfo3['rating'];
$wlevel = $iteminfo3['level'];
$location = $iteminfo3['location'];
$randid = rand(1000,999999999);

$percentage = $iteminfo3['price'] / 100;
	  $price = $iteminfo3['price'] * $percentage;
      $newprice = $price + $iteminfo3['price'];
	  $newprice = (int)$newprice;

$itembought = "INSERT into playerarmor(pid, name, rating, level, randid,price,class,location) VALUES ('$playerid','$name','$rating','$wlevel','$randid','$newprice','$class','$location')";
mysql_query($itembought) or die("could not insert weapon into backpack");

$shield = mysql_query("SELECT * FROM playerarmor WHERE location='shield'");
$shiled_query = mysql_query($shield) or die("could not insert weapon into backpack");
while( mysql_fetch_array($shield_query))
      {
      $shield_query="INSERT into secondhand(pid, name, rating, level, randid,price,class,location) VALUES ('$playerid','$name','$rating','$wlevel','$randid','$newprice','$class','$location')";
      }

$updateplayer="update players set gold=gold-'$newprice' where name='$player'";
  mysql_query($updateplayer) or die("Could not update player");

echo $name . " Purchased<br><br>";
echo $inninfo3['exitgreet'] . "<br><br>";
 echo "<a href='index.php'>Go Back</center>";
}



 if ($bypass != 1)
 {
 echo "<center>" . $inninfo3['greeting'] . "<br>";


 echo "<small>";
      print "<center>";
      print "<table border='0' width='70%' cellspacing='20'>";
      print "<tr><td width='25%' valign='top'>";
      print "</td>";
      print "<td valign='top' width='75%'>";
      $invinfo="SELECT * from armor where class = '$class' AND level <= '$level' AND level <= '$shoplevel' || class='any'";
      $invinfo2=mysql_query($invinfo) or die("could not select anything from the store.");
      print "<table border='1' bordercolor='white' bgcolor='#ffffff'>";
      print "<tr><td>Name<font color='ffffff'>________________</td><td>Rating<font color='ffffff'>______</td><td>Location<font color='ffffff'>______</td><td>Price<font color='ffffff'>______</td><td><font color='ffffff'>________________</td></tr>";
      while($invinfo3=mysql_fetch_array($invinfo2))
      {
      $counter = 1;
	  $percentage = $inninfo3['price'] / 100;
	  $price = $invinfo3['price'] * $percentage;
      $newprice = $price + $invinfo3['price'];
	  $newprice = (int)$newprice;
	  print "<tr><td>$invinfo3[name]</td><td>$invinfo3[rating]</td><td>$invinfo3[location]</td><td>$newprice</td><td><A href='armorshop.php?id=$invinfo3[id]&bought=1'>Buy Armor</td></tr>";

      }

      print "</table>";
      print "</td></tr></table>";
      print "</center>";
      echo "</small>";

      if ($counter == 0)
      {
            echo "<center>There is nothing in the store at this time.<br>";
            echo "<a href='index.php'>Go Back</center>";
            exit;
      }
  echo "<center><a href='index.php'>NeverMind</center></a>";
  }
  ?>
  </div>

i put all armorshop.php code here, maybe somewhere in another row isint good..

such errors can easily be googled...
look at your line 89

Code: Select all

88 $shield = mysql_query("SELECT * FROM playerarmor WHERE location='shield'");
89 $shiled_query = mysql_query($shield) or die("could not insert weapon into backpack");
90 while( mysql_fetch_array($shield_query))
 

u misstyped SHIELD_QUERRY..

just try to check your code on your own before posting u could have seen this if you just had scrolled to the line where the error was..., u must learn to understand the error codes then its pretty much self explaining what is going wrong.

BTW i think it is better to make your own Thread for the next time

regards

[Jackolantern]

BTW i think it is better to make your own Thread for the next time

regards

Case in point: I didn't even realize it wasn't the same poster lol