Storing form array problem

partypageusa

Hi, I'm having problems with my one part of my form. The problem I'm having is with the multiple choice select box, I can't seem to get the select box to insert/update the mysql database with the multiple selection. Here is a sample of the form I am using. Thanks in advance.

<html>
<head>
<?php 
include	("include/config.inc.php");	//Include files
?>
<title>Add/Update Info</title>
<link rel="stylesheet" href="style.css" type="text/css" />
</head>
<body>
<?php
if($submit) 
{ 
$db = @mysql_connect("$dbhost", "$dbuser", "$dbpassword"); 
@mysql_select_db("$dbname",$db); 
$sql = "INSERT INTO bar (id, letter, name, category, address) VALUES ('$id','$letter','$name','$category','$address')";
$result = mysql_query($sql); 
echo "<h1>Thank you! Information entered.\n</h1>";
echo "<P>Your entry has been added.  Would you like to <a href=\"addedit.php\">add another</a>?</p>";
} 
else if($update)
{
$db = mysql_connect("$dbhost", "$dbuser","$dbpassword"); 
mysql_select_db("$dbname",$db);
$sql = "UPDATE bar SET id='$id', letter='$letter', name='$name', category='$category', address='$address' WHERE id=$id";
$result = mysql_query($sql);
echo "Thank you! Information updated.\n";
echo "<P>Your entry has been updated.  Would you like to <a href=\"viewdb.php\">update another</a>?</p>";
}
else if($id)
{
$db = mysql_connect("$dbhost", "$dbuser","$dbpassword"); 
mysql_select_db("$dbname",$db);
$result = mysql_query("SELECT * FROM bar WHERE id=$id",$db);
$myrow = mysql_fetch_array($result);
?>
<form method="post" action="<?php echo $PHP_SELF?>">
<input type="hidden"name="id"value="<?php echo $myrow["id"]?>">
<table>
	<tr>
		<th colspan=2>
			Update A Listing
		</th>
	</tr>
	<tr>
		<td class="required" colspan=2>
			<b>* if red then you MUST enter data</b><br /><br />
		</td>
	</tr>
		<tr>
		<td class="required">
			<b>*Bar Letter</b>
		</td>
		<td>
			<input type="text" name="letter" size=2 maxlength=1" value="<?php echo $myrow["letter"]?>">
		</td>
	</tr>
	<tr>
		<td class="required">
			<b>*Bar Name</b>
		</td>
		<td>
			<input type="text" name="name" size=30 maxlength=100" value="<?php echo $myrow["name"]?>">
		</td>
	</tr>
	<tr>
		<td class="required">
			<b>*Category</b>
		</td>
		<td>
			<?php	include("categories.php");?>
		</td>
	</tr>
	<tr>
		<td class="required">
			<b>*Address</b>
		</td>
		<td>
			<input type="text" name="address" size=30 maxlength=100" value="<?php echo $myrow["address"]?>">
		</td>
	</tr>
	<tr class="favcolor2">
		<td colspan=2>
			<input class="submit" type="submit" name="update" value="Update Information"> 
			<p><br></p>
		</td>
	</tr>
</table>
</form>
<?
}
else
{
?>
<form method="post" action="<?php echo $PHP_SELF?>">
<table>
	<tr>
		<th colspan=2>
			Add A Listing
		</th>
	</tr>

<tr>
	<td class="required" colspan=2>
		<b>* if red then you MUST enter data</b><br><br>
	</td>
</tr>

<tr>
	<td class="required">
		<b>*Bar Letter</b>
	</td>
	<td>
		<input type="text" name="letter" size=2 maxlength=1">
	</td>
</tr>
<tr>
	<td class="required">
		<b>*Bar Name</b>
	</td>
	<td>
		<input type="text" name="name" size=30 maxlength=100">
	</td>
</tr>
<tr>
	<td class="required">
		<b>*Categories</b>
	</td>
	<td>
		<SELECT NAME="category[]" class="input" size="3" multiple="yes">
		<option value="Bar">Bar</option>
		<option value="Club">Club</option>
		<option value="Live Music">Live Music</option>
		<option value="Lounges">Lounges</option>
		<option value="Restaurant">Restaurant</option>
		<option value="Other">Other</option>
		</SELECT>
	</td>
</tr>

<tr>
	<td class="required">
		<b>*Address</b>
	</td>
	<td>
		<input type="text" name="address" size=30 maxlength=100">
	</td>
</tr>
<tr class="favcolor2">
	<td colspan=2>
		<input class="submit" type="submit" name="submit" value="Enter Information"> 
		<p><br /></p>
	</td>
</tr>
</table>
</form>
<? 
} 
?>
</body>
</html>

tsinka

Hi,

first of all I'd suggest to use $_POST['variable'] instead of $variable with the posted variables.

The categories are submitted as array so you need to transform that array to a string first.

You might try something like this:

$categories = implode("",$POST['category']);
Store $categories in the table.
If you fetch the data from the table use e.g.

$categories = explode("_",$row['category']);

to get back the array of categories.

Thomas

partypageusa

So you mean change the

$sql = "INSERT INTO bar (id, letter, name, category, address) VALUES ('$id','$letter','$name','$category','$address')";

$sql = "UPDATE bar SET id='$id', letter='$letter', name='$name', category='$category', address='$address' WHERE id=$id";

$sql = "INSERT INTO bar (id, letter, name, category, address) VALUES ($_POST['id'],$_POST['letter'],$_POST['name'],$_POST['category'],$_POST['address'])";

$sql = "UPDATE bar SET id=$_POST['id'], letter=$_POST['letter'], name=$_POST['name'], category=$_POST['category'], address=$_POST['address'] WHERE id=$_POST['id']";

And where on the form do I put
$categories = implode("",$POST['category']);
and
$categories = explode("_",$row['category']);

Thanks for the help.

tsinka

Hi,

put single quotes around the comma delimited values inside the VALUES (...) block.

When doing an insert or update do somthing like this:

$categories = implode("_",$_POST['category']);
// Don't use the $_POST variables inside the string
// use concatenation
$sql = "INSERT INTO bar (id, letter, name, category, address) VALUES "
      ." ('".$_POST['id']."','".$_POST['letter']."','".$_POST['name']
      ."','".$categories."','".$_POST['address']."')";

use $categories instead of $_POST['category'] in the query.
Same for the update.

Whenever you do a SELECT you get the array of categories with e.g.

$sql = "SELECT id,letter,name,category,address FROM bar WHERE id='10'";
$res = mysql_query($sql);
$row = mysql_fetch_array($res);
$arrCategories = explode("_",$row['category']);

Thomas

partypageusa

after using the changes i am not getting any of the select input store in the database. The database for category is just blank. Here is the script I have now.

<?php
//If new bar is added submit it
if($submit) 
{ 
$db = @mysql_connect("$dbhost", "$dbuser", "$dbpassword"); 
@mysql_select_db("$dbname",$db);
$categories = implode("_",$_POST['category']);
$sql = "INSERT INTO bar (id, letter, name, location, category, description, rank, address, crossaddress, city, state, zip, areacode, phone, url, image, expires, lastupdate) VALUES "."('".$_POST['id']."','".$_POST['letter']."','".$_POST['name']."','".$_POST['location']."','".$categories."','".$_POST['description']."','".$_POST['rank']."','".$_POST['address']."','".$_POST['crossaddress']."','".$_POST['city']."','".$_POST['state']."','".$_POST['zipcode']."','".$_POST['areacode']."','".$_POST['phone']."','".$_POST['url']."','".$_POST['logo']."','".$_POST['expire']."','".$_POST['lastupdate']."')";
$result = mysql_query($sql); 
echo "<h1>Thank you! Information entered.\n</h1>";
echo "<P>Your entry has been added.  Would you like to <a href=\"addedit.php\">add another</a>?</p>";
} 
else if($update)
{
$db = mysql_connect("$dbhost", "$dbuser","$dbpassword"); 
mysql_select_db("$dbname",$db);
$category = implode("_",$_POST['category']);
$sql = "UPDATE bar SET id='".$_POST['id']."', letter='".$_POST['letter']."', name='".$_POST['name']."', location='".$_POST['location']."', category='".$categories."', description='".$_POST['description']."', rank='".$_POST['rank']."', address='".$_POST['address']."', crossaddress='".$_POST['crossaddress']."', city='".$_POST['city']."', state='".$_POST['state']."', zip='".$_POST['zipcode']."', areacode='".$_POST['areacode']."', phone='".$_POST['phone']."', url='".$_POST['url']."', image='".$_POST['logo']."', expires='".$_POST['expire']."', lastupdate='".$_POST['lastupdate']."' WHERE id='".$_POST['id']."'";
$result = mysql_query($sql);
echo "Thank you! Information updated.\n";
echo "<P>Your entry has been updated.  Would you like to <a href=\"viewdb.php\">update another</a>?</p>";
}
else if($id)
{
$db = mysql_connect("$dbhost", "$dbuser","$dbpassword"); 
mysql_select_db("$dbname",$db);
$result = mysql_query("SELECT * FROM bar WHERE id=$id",$db);
$myrow = mysql_fetch_array($result);
?>
<form method="post" action="<?php echo $PHP_SELF?>"> 
<input type="hidden"name="id"value="<?php echo $myrow["id"]?>"> 
<table> 
    <tr> 
        <th colspan=2> 
            Update A Listing 
        </th> 
    </tr> 
    <tr> 
        <td class="required" colspan=2> 
            <b>* if red then you MUST enter data</b><br /><br /> 
        </td> 
    </tr> 
        <tr> 
        <td class="required"> 
            <b>*Bar Letter</b> 
        </td> 
        <td> 
            <input type="text" name="letter" size=2 maxlength=1" value="<?php echo $myrow["letter"]?>"> 
        </td> 
    </tr> 
    <tr> 
        <td class="required"> 
            <b>*Bar Name</b> 
        </td> 
        <td> 
            <input type="text" name="name" size=30 maxlength=100" value="<?php echo $myrow["name"]?>"> 
        </td> 
    </tr> 
    <tr> 
        <td class="required"> 
            <b>*Category</b> 
        </td> 
        <td> 
            <SELECT NAME="category[]" class="input" size="3" multiple="yes"> 
            <option value="Bar">Bar</option> 
            <option value="Club">Club</option> 
            <option value="Live Music">Live Music</option> 
            <option value="Lounges">Lounges</option> 
            <option value="Restaurant">Restaurant</option> 
            <option value="Other">Other</option> 
            </SELECT>  

        </td> 
    </tr> 
    <tr> 
        <td class="required"> 
            <b>*Address</b> 
        </td> 
        <td> 
            <input type="text" name="address" size=30 maxlength=100" value="<?php echo $myrow["address"]?>"> 
        </td> 
    </tr> 
    <tr class="favcolor2"> 
        <td colspan=2> 
            <input class="submit" type="submit" name="update" value="Update Information"> 
            <p><br></p> 
        </td> 
    </tr> 
</table> 
</form> 
<? 
} 
else 
{ 
?> 
<form method="post" action="<?php echo $PHP_SELF?>"> 
<table> 
    <tr> 
        <th colspan=2> 
            Add A Listing 
        </th> 
    </tr> 

<tr> 
    <td class="required" colspan=2> 
        <b>* if red then you MUST enter data</b><br><br> 
    </td> 
</tr> 

<tr> 
    <td class="required"> 
        <b>*Bar Letter</b> 
    </td> 
    <td> 
        <input type="text" name="letter" size=2 maxlength=1"> 
    </td> 
</tr> 
<tr> 
    <td class="required"> 
        <b>*Bar Name</b> 
    </td> 
    <td> 
        <input type="text" name="name" size=30 maxlength=100"> 
    </td> 
</tr> 
<tr> 
    <td class="required"> 
        <b>*Categories</b> 
    </td> 
    <td> 
        <SELECT NAME="category[]" class="input" size="3" multiple="yes"> 
        <option value="Bar">Bar</option> 
        <option value="Club">Club</option> 
        <option value="Live Music">Live Music</option> 
        <option value="Lounges">Lounges</option> 
        <option value="Restaurant">Restaurant</option> 
        <option value="Other">Other</option> 
        </SELECT> 
    </td> 
</tr> 

<tr> 
    <td class="required"> 
        <b>*Address</b> 
    </td> 
    <td> 
        <input type="text" name="address" size=30 maxlength=100"> 
    </td> 
</tr> 
<tr class="favcolor2"> 
    <td colspan=2> 
        <input class="submit" type="submit" name="submit" value="Enter Information"> 
        <p><br /></p> 
    </td> 
</tr> 
</table> 
</form> 
<?  

}  

?> 
</body> 
</html>

tsinka

Replace

$result = mysql_query($sql);

$result = mysql_query($sql) or die("SQL error: ".mysql_error());

and see if there are any errors.

What type is the category column of ?

Thomas

partypageusa

I am not getting any error messages when I am using
$result = mysql_query($sql) or die("SQL error: ".mysql_error());
but still not getting any of the select inputs being store in the database.

The category column type is varchar().

tsinka

Is register_globals set to On or Off in php.ini ?

Add a

echo $sql;

just below the $sql="..."; line(s) and post the output.

Thomas

partypageusa

the output is
UPDATE bar SET id='2', letter='#', name='101 Bar', location='Greenwich Village', category='', address='101 7th Ave. S' WHERE id='2'

and the register_globals is set to On

tsinka

Change the line
$category = implode("",$POST['category']);
to
$categories = implode("",$POST['category']);

partypageusa

Thank you so much, it's working now.

Another question relating to the output. If I want the output data to be in comma format do I just change the

$arrCategories = explode("_",$row['category']);

$arrCategories = explode(", ",$row['category']);

or should I just change how the data is being store from underscore to comma in the

$categories = implode("",$POST['category']);

$categories = implode(", ",$_POST['category']);

This is what I am using

<?php
//Connection to data
$db = mysql_connect("$dbhost", "$dbuser","$dbpassword"); 
mysql_select_db("$dbname",$db);
$result = mysql_query("SELECT * FROM bar WHERE id=$id",$db); 
$row = mysql_fetch_array($result);
$arrCategories = explode(",_",$row['category']); 
//Show the Bar
?> 
<table width="100%">
   <tr>
      <td width="75%">
		<span style="font-size:14px;">
			<img src="image/iconBook.png" width="16" height="16" border=0 alt="Visit<?php echo $row["name"]?>"> 
<b><?php echo $row["name"]?></b>
			<br />Category: <b><?php echo $row["category"]?></b>
			<br /><br /><?php echo $row["address"]?>
			<br /><?php echo $row["city"]?> 


	</span>
</td>
   </tr>
</table>


</body>
</html>

tsinka

Hi,

just use implode(", ",$POST['category']); if you want to display the categories comma separated. So you won't need explode at all if you just want to display the data after doing the select. It doesn't matter if you use , or , just use a character that none of the category names contains.

You only need to use explode if you want to have the categories in an array for some reason.

Your code looks fine but you don't need the explode in this case.

Thomas

partypageusa

ok thanks a bunch thomas.