Using arrays to populate database from input box error

[PMacca_1987]

Hi,

I have two pieces of code, one calls the other and passes some information across.

I have a customisable table in the database that any user can create. The problem that I am receiving now is that I am using a foreach statement to loop through the table and drop the text fields on the page.

Then the separate inputs need to be transferred to the next page and inserted into the database as text into the relating field.

The code I already have, I feel is close but I am having errors as I will show.

Thanks in advance with your help with this, I've been on it hours so far and have run out of ideas!

<?php
$result = mysql_query("SHOW COLUMNS FROM <variable named table> WHERE Field NOT IN ('Column1','Column2')");
$count = 0;
while ($row=mysql_fetch_row($result)){
    $cnt = 0;
    foreach ($row as $item){
        if ($cnt == 0){
            $cnames[$count] = $item;
            $cnt++;
            $count++;
        }
    }
}
    ?>
              <?php
              $i = 0;
                         foreach($cnames as $c){
                  echo $c?> <input name="ProductValue[<?php echo $c;?>]" type="text"><br>
          <?php $i++?>
             <?php } ?>

     <input name="Save" type="image"  src="images/add.png" align="right">
     <?php } 
?>

The page that the above form leads to is:

$ProductValue = $_GET['ProductValue'];

          <?php foreach($ProductValue as $name=>$value){
              $query = "INSERT INTO <variable table name> (".$name.") VALUES ('".$value."')";
          }
            $result = @mysql_query($query, $connection)
            or die ("Unable to perform query<br>$query");
  header("Location: PRODUCTS.php");
    exit();

The errors I am receiving are as follows:

Warning: mysql_fetch_row() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\SALESDOCK\AddProductForm.php on line 21

Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\SALESDOCK\AddProductForm.php on line 34

Thankyou for looking at this

[BIOSTALL]

Try changing:

$result = mysql_query("SHOW COLUMNS FROM <variable named table> WHERE Field NOT IN ('Column1','Column2')"); 

To:

$result = mysql_query("SHOW COLUMNS FROM <variable named table> WHERE Field NOT IN ('Column1','Column2')") or die(mysql_error()); 

This will show you an error generated by MySQL telling you what is wrong. If you don't understand the error post it here and I'm sure someone will be able to tell you what the problem is.

Also, I don't think you're declaring $cnames as an array. After '$count = 0;' on the third line, try adding '$cnames = array();' (minus the quotes).

[halojoy]

I found a similar query in MySQL forum:
"SHOW COLUMNS FROM <my_table> WHERE Field NOT IN ('column1','column2')"
And it should work.

Your error idicates that the query fails.
And so the $result = FALSE; and can not be used by mysql_fetch_row() in Line 21

Why dont you use [man]mysql_error[/man]
Any sensible php developer use it after each query.
Because there is where most problems come.

<?php

$result = mysql_query("SHOW COLUMNS FROM <variable named table> WHERE Field NOT IN ('Column1','Column2')");
if(!$result) exit('Error:<br />'.$query.'<br />'.mysql_error());

$count = 0;

[dagon]

your query is failing, do this to debug

$q="SHOW COLUMNS FROM <variable named table> WHERE Field NOT IN ('Column1','Column2')";
$result = mysql_query($q) or die("A MySQL error has occurred.<br />Your Query: " . $q . "<br /> Error: (" . mysql_errno() . ") " . mysql_error())

[dagon]

must be your lucky day, 3 replies at once, glad we all agree on the issue :-)

[PMacca_1987]

Thankyou everyone for your answers!

I do normally include the error catcher as I tend to understand the errors, someone "fixed" some of this code in another "obviously less skilled" forum so it's my fault for not realising those parts were missing.. I'll go work on it now.. Thanks guys

[PMacca_1987]

OK I ran it with the error catcher and the problem was the same.. I then tried the SQL itself in PHP myadmin and the code runs fine. Could there be another issue or did it look like that was the only problem?
Thanks

[dagon]

what was the error message? what does the querry look like?

[PMacca_1987]

Right, I apologise, I just got that bit of code working, had some bracket related trauma :s

The code doesn't do what I wanted still which is frustrating!

The code is only displaying one of the fields for the user to type information into.. This one field is working correctly as it is storing the field in the database.

<?php ob_start("ob_gzhandler");
session_start();
        function AddProduct()
        {
        require "connect.php";
?>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title></title>
    </head>
        <body>

<form action="AddProduct.php" method="get"  name="AddProduct" >

 <?php
$result = mysql_query("SHOW COLUMNS FROM ".$_SESSION['Username']." WHERE Field NOT IN ('UserID','ProductID')");
if(!$result) exit('Error:<br />'.$query.'<br />'.mysql_error());
$count = 0;
while ($row = mysql_fetch_row($result)){
    $cnt = 0;
    $cnames = array();
    foreach ($row as $item){
        if ($cnt == 0){
            $cnames[$count] = $item;
            $cnt++;
            $count++;
        }
    }
}
    ?>
              <?php
                         foreach($cnames as $c){
                  echo $c?> <input name="ProductValue[<?php echo $c;?>]" type="text"><br>
             <?php } ?>

     <input name="Save" type="image"  src="images/add.png" align="right">
     <?php } 
?>
</form>

[dagon]

you reset $cnt inside the while to 0 so you will only ever get 1

you don't need the 3 loops (while,foreach*2) you can do it all with just the while.

[PMacca_1987]

OK once again I believed I had it working! with this for the loop/s

<?php
$result = mysql_query("SHOW Fields FROM ".$_SESSION['Username']." WHERE Field NOT IN ('UserID','ProductID')");
if(!$result) exit('Error:<br />'.$query.'<br />'.mysql_error());

while ($row = mysql_fetch_row($result)){
$count = 0;
$cnt = 0;
$cnames = array();
foreach ($row as $item){
$cnames[$count] = $item;
$cnt++;
$count++;
}
}
foreach($cnames as $c){
echo $c?> <input name="ProductValue[<?php echo $c;?>]" type="text"><br>

         <?php } ?>[/QUOTE]

I believe that the problem now is that the SQL is producing more than just the names of the tables and is giving the type and the null value of the field..

I will try to work this out myself but if anyone does come up with anything for the SQL side I would be grateful.

Thanks for all for all of the help that you have all given me.
Paul

[dagon]

looks to me like all you need is this:

<?php
$result= mysql_query("SHOW Fields FROM " . $_SESSION['Username'] . " WHERE Field NOT IN ('UserID','ProductID')");
if( !$result)
	exit('Error:<br />' . $query . '<br />' . mysql_error());


while($row= mysql_fetch_row($result)){
	echo '<input name="'.$row['Field'].'" type="text">';
}
?>