Pulling and updating data from a seperate DB Table

spacepoet · May 28, 2011

Hello:

I have a DB table with this structure:

CREATE TABLE `gallery_category` (
  `category_id` bigint(20) unsigned NOT NULL auto_increment,
  `category_name` varchar(50) NOT NULL default '0',
  PRIMARY KEY  (`category_id`),
  KEY `category_id` (`category_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

What I am trying to do id pull this data into my "Edit Product" page and populate a SELECT menu with it.

I want the user to be able to re-assign a product to a new category if they chose to do so.

I want to add the data (and update the DB) to this area:

         <div style="float: left; width: 550px;">
         	<select name='category_name'>
		<option></option>
	</select>

         </div>

This is the full page code that allows users to update product info:

<?php

include('../include/myConn.php');
include('../include/myCodeLib.php');
include('include/myCheckLogin.php');
include('include/myAdminNav.php');
include('ckfinder/ckfinder.php');
include('ckeditor/ckeditor.php');

$photo_id = $_REQUEST['photo_id'];

if ($_SERVER['REQUEST_METHOD'] == 'POST')
{
$photo_title = mysql_real_escape_string($_POST['photo_title']);
$photo_price = mysql_real_escape_string($_POST['photo_price']);
$photo_caption = mysql_real_escape_string($_POST['photo_caption']);


$sql = "

UPDATE gallery_photos
   SET
      photo_title = '$photo_title',
      photo_price = '$photo_price',
      photo_caption = '$photo_caption'
  WHERE photo_id = $photo_id
  ";
  
mysql_query($sql) && mysql_affected_rows()

?>
<?php
}

$query=mysql_query("SELECT photo_title,photo_price,photo_caption FROM gallery_photos") or die("Could not get data from db: ".mysql_error());

while($result=mysql_fetch_array($query))
{
  $photo_title=$result['photo_title'];
  $photo_price=$result['photo_price'];
  $photo_caption=$result['photo_caption'];
}
?>

<!DOCTYPE HTML>

<html>
<head>

</head>

<body>

<div id="siteContainer">

		<p>
		<?php
		if ($_SERVER['REQUEST_METHOD'] == 'POST')
		echo "<span class=\"textError\">". $photo_title ." successfully updated!</span>"
		?>
		</p>

		<p>

         <form method="post" action="<?php echo $PHP_SELF;?>">
         <input type="hidden" name="POSTBACK" value="EDIT">
         <input type='hidden' name='photo_id' value='<?php echo $photo_id; ?>' />



         <div style="float: left; width: 120px; margin-right: 30px;">
         	Category:
         </div>
         
         <div style="float: left; width: 550px;">
         	<select name='category_name'>
		<option></option>
	</select>

         </div>





         
         <div style="float: left; width: 120px; margin-right: 30px;">
         	Title:
         </div>
         
         <div style="float: left; width: 550px;">
         	<input type="text" name="photo_title" size="45" maxlength="200" value="<?php echo $photo_title; ?>" />
         </div>
         
         <div style="clear: both;"><br /></div>
         
         <div style="float: left; width: 120px; margin-right: 30px;">
         	Price:
         </div>
         
         <div style="float: left; width: 550px;">
         	<input type="text" name="photo_price" size="45" maxlength="200" value="<?php echo $photo_price; ?>" />
         </div>
         
         <div style="clear: both;"><br /></div>
         

         	Description:<br />
         	<textarea cols="107" rows="1" name="photo_caption"><?php echo $photo_caption; ?></textarea>


         
         <div style="clear: both;"><br /></div>
         
         	
            
         <br />
         
         <input type="submit" value="Submit" />
         
         </form>
         
</p>


</div>

</body>

</html>

How can I do this? I'm stumped ...

Thanks!

mikesta707 · May 28, 2011

Firstly, when posting code, please only post the relevant code. No one wants to read through lines and lines of code just to FIND what you are talking about.

Now, selecting something from a DB is rather eas. As I would hope you already know, simply use a select statement to grab all the information you need. For example

$sql = "SELECT * FROM mytable WHERE some condition";
$res = mysql_query($sql);
$row = mysql_fetch_array($res);

now we can use this $row variable to populate our option box. To do this, we can use a foreach loop to create as many options as we need. for example, assuming we did the code above to grab the db row and store it into the array $row, we can

echo "<select ... whatever attributes you want>";
foreach($row as $key=>$value){//i use $key=>$value syntax just incase you need the key for some reason
echo "<option attributes here... value='$value'>$value</option>";
}//end foreach

that should get you started. Hope that helps

spacepoet · May 29, 2011

Hi:

Thanks for pointing me in the right direction .. still not getting it to work ..

Everything is OK, except for populating the VALUES in the OPTION.

...
<?php
}

$query=mysql_query("SELECT photo_title,photo_price,photo_caption,photo_category FROM gallery_photos") or die("Could not get data from db: ".mysql_error());

while($result=mysql_fetch_array($query))
{
  $photo_title=$result['photo_title'];
  $photo_price=$result['photo_price'];
  $photo_caption=$result['photo_caption'];
  $photo_category=$result['photo_category'];
}
?>
...

...

         <?
         	echo "<select name='photo_category' size='1'>";
         	while( $row = mysql_fetch_array( $result ) )
		{
         	echo "<option value=\"$row\">$row</option>";
         	}
         	echo "</select>";	
	 ?>

...

I'm getting this error:

<b>Warning</b>:  mysql_fetch_array(): supplied argument is not a valid MySQL result resource in <b>...admin/a_Photo_Edit2.php</b> on line <b>112</b><br />

What am I missing ??

mikesta707 · May 29, 2011

There are many things wrong with what you wrote.

Firstly, your error. When you use a loop to get all the rows from your result set, at the end of the while loop, the variable you used is set to false. For example

$sql = mysql_query("some query");
while ($row = mysql_fetch_array($sql)){
//do some processing here
}
echo $sql;

this would echo false, or null, or something along those lines depending on your system and version of PHP. This is because your result resource is kind of like a pointer to the current row in the result set, and when you loop through all the rows, the result set has nothing to point at any more. To fix this, you either need to do the query again or, more optimally, just do the select echoing in your first query fetch array loop. For example

echo "<select name='photo_category' size='1'>";
while($result=mysql_fetch_array($query))
{
  $photo_title=$result['photo_title'];
  $photo_price=$result['photo_price'];
  $photo_caption=$result['photo_caption'];
  $photo_category=$result['photo_category'];
  //also do select stuff
  //im assuming you want to use photo_category as the value of the option
  //explanation for why $row is wrong is below
  echo "<option value=\"$photo_category\">$photo_category</option>";
}
?>

Now, conceptually, you don't seem to understand what mysql_fetch_array() returns to you. It returns an array, but you are using it like a string in the following snippet you posted

<?
         	echo "<select name='photo_category' size='1'>";
         	while( $row = mysql_fetch_array( $result ) )
		{
         	echo "<option value=\"$row\">$row</option>";
         	}
         	echo "</select>";	
	 ?>

You need to supply an index, rather than trying to echo out the whole array. If you were to leave this snippet like it was (assuming you fixed your other problem) You would have a select box all with values equal to "Array" and the text for the options would be "Array". To fix this, you need to determine which column you want to populate the options with from your result set, and use those options as keys to the array $row. For example, if the column you wanted was called 'photo_category' (and it seems like it might be in your case) you would do

<?
         	echo "<select name='photo_category' size='1'>";
         	while( $row = mysql_fetch_array( $result ) )
		{
         	echo "<option value=\"".$row['photo_category']."\">".$row['photo_category']."</option>";
         	}
         	echo "</select>";	
	 ?>

spacepoet · May 29, 2011

I'm still not getting it ...

I'm trying to do it this way:

         	<?

		$result = mysql_query( "SELECT category_id,category_name FROM gallery_category" );
		while( $row = mysql_fetch_array( $result ) )
		{
		echo "<select name='photo_category' size='1'>";
		while( $row = mysql_fetch_array( $result ) )
		{
		echo "<option value=\"".$row['category_id']."\">".$row['category_name']."</option>";
		}
		echo "</select>";
		}

	 ?>

to pull the category names from the gallery_category TABLE and be able to update the category name an item is assigned to.

Can it be done this way?

I thought it could, but I somehow need to JOIN the two TABLES, which is where I am rather lost.

Everything works fine with the original code I posted, but I can't figure out how to get this last part to work correctly.

Ideas .. ??

mikesta707 · May 30, 2011

well you seem to be nesting while loops for some strange reason. I never indicated you should do this. Based on the code you posted, perhaps you meant to do

$result = mysql_query( "SELECT category_id,category_name FROM gallery_category" );
echo "<select name='photo_category' size='1'>";
while( $row = mysql_fetch_array( $result ) ){
echo "<option value=\"".$row['category_id']."\">".$row['category_name']."</option>";
}
echo "</select>";

spacepoet · May 30, 2011

Hi:

This works to display the dropdown properly. Thank you.

But, I still can not get the dropdown to update properly.

"photo_category" is a number that is used to determine what CATEGORY an item should be assigned to.

Looking at the current code, I think that "category_id" should be "photo_category":

         <?

$result = mysql_query( "SELECT category_id,category_name FROM gallery_category" );
echo "<select name='photo_category' size='1'>";
while( $row = mysql_fetch_array( $result ) ){
//echo "<option value=\"".$row['photo_category']."\">".$row['category_name']."</option>";

echo "<option value=\"$photo_category\">".$row['category_name']."</option>";

}
echo "</select>";	

	 ?>

When I do that, here is no number displayed in the source code:

<select name='photo_category' size='1'>
<option value="0">CATEGORY 2</option>
<option value="0">CATEGORY 1</option>
</select>

However, when I am inserting the item, the dropdown populates fine:

<select name='category'>
<option value="39">CATEGORY 2</option>
<option value="38">CATEGORY 1</option>
</select>

So, I seem to be missing one last thing to get this to work.

Am I missing some type of syntax to get the values to display properly?

Thanks for your help on this!

mikesta707 · May 30, 2011

well in this code

<?

$result = mysql_query( "SELECT category_id,category_name FROM gallery_category" );
echo "<select name='photo_category' size='1'>";
while( $row = mysql_fetch_array( $result ) ){
//echo "<option value=\"".$row['photo_category']."\">".$row['category_name']."</option>";

echo "<option value=\"$photo_category\">".$row['category_name']."</option>";

}
echo "</select>";	

	 ?>

You never define $photo_category from what I can see. Perhaps you should do

<?

$result = mysql_query( "SELECT category_id,category_name FROM gallery_category" );
echo "<select name='photo_category' size='1'>";
while( $row = mysql_fetch_array( $result ) ){
//echo "<option value=\"".$row['photo_category']."\">".$row['category_name']."</option>";
$photo_category = $row['photo_category'];
echo "<option value=\"$photo_category\">".$row['category_name']."</option>";

}
echo "</select>";	

	 ?>

spacepoet · May 30, 2011

No, tried that but it's still empty (value="") ..

Can I do it where I use the "category_id" (like I just posted) since it works properly,

but somehow change the UPDATE code to make the "category_id" equal the "photo_category":

<?php

$photo_id = $_REQUEST['photo_id'];

if ($_SERVER['REQUEST_METHOD'] == 'POST')
{
$photo_id = mysql_real_escape_string($_POST['photo_id']);
$photo_title = mysql_real_escape_string($_POST['photo_title']);
$photo_price = mysql_real_escape_string($_POST['photo_price']);
$photo_caption = mysql_real_escape_string($_POST['photo_caption']);
$photo_category = mysql_real_escape_string($_POST['photo_category']);

$sql = "

UPDATE gallery_photos
   SET
   	  photo_id = '$photo_id',
      photo_title = '$photo_title',
      photo_price = '$photo_price',
      photo_caption = '$photo_caption',
      photo_category = '$photo_category'
  WHERE photo_id = $photo_id
  ";
  
mysql_query($sql) && mysql_affected_rows()

?>
<?php
}

$query=mysql_query("SELECT photo_id,photo_title,photo_price,photo_caption,photo_category FROM gallery_photos WHERE photo_id=$photo_id") or die("Could not get data from db: ".mysql_error());

while($result=mysql_fetch_array($query))
{
  $photo_id=$result['photo_id'];
  $photo_title=$result['photo_title'];
  $photo_price=$result['photo_price'];
  $photo_caption=$result['photo_caption'];
  $photo_category=$result['photo_category'];
}
?>

...

<form method="post" action="<?php echo $PHP_SELF;?>">

<?

$result = mysql_query( "SELECT category_id,category_name FROM gallery_category" );
echo "<select name='photo_category' size='1'>";
while( $row = mysql_fetch_array( $result ) )
{
echo "<option value=\"".$row['category_id']."\">".$row['category_name']."</option>";
}
echo "</select>";


?>

</form>

Somehow change the UPDATE SQL to do that?

Would that work?

Sorry, I know I'm being a pain but this is the last part and it's driving me nuts!

mikesta707 · May 30, 2011

oh, i didn't notice this, but you arent selecting photo_category in your sql select statement. perhaps this

$result = mysql_query( "SELECT category_id,category_name FROM gallery_category" );

should be

$result = mysql_query( "SELECT category_id,category_name,photo_category FROM gallery_category" );

I'm not entirely sure where the photo_category column resides though, so I'm more or less taking shots in the dark

spacepoet · May 30, 2011

Hi:

No, I know that isn't it.

These are the 2 tables:

gallery_category

-- 
-- Table structure for table `gallery_category`
-- 

CREATE TABLE `gallery_category` (
  `category_id` bigint(20) unsigned NOT NULL auto_increment,
  `category_name` varchar(50) NOT NULL default '0',
  PRIMARY KEY  (`category_id`),
  KEY `category_id` (`category_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

-- 
-- Dumping data for table `gallery_category`
-- 

INSERT INTO `gallery_category` VALUES (1, 'My First Gallery');

gallery_photos

-- 
-- Table structure for table `gallery_photos`
-- 

CREATE TABLE `gallery_photos` (
  `photo_id` bigint(20) unsigned NOT NULL auto_increment,
  `listorder` int(11) default NULL,
  `photo_filename` varchar(25) default NULL,
  `photo_title` varchar(255) default NULL,
  `photo_price` varchar(255) default NULL,
  `photo_caption` text,
  `photo_category` bigint(20) unsigned NOT NULL default '0',
  PRIMARY KEY  (`photo_id`),
  KEY `photo_id` (`photo_id`)
) ENGINE=MyISAM AUTO_INCREMENT=105 DEFAULT CHARSET=latin1 AUTO_INCREMENT=105 ;

-- 
-- Dumping data for table `gallery_photos`
-- 

INSERT INTO `gallery_photos` VALUES (97, NULL, '97.jpg', '', 1);

Where "category_id" is the same number as "photo_category"

The "photo_category" number is created/selected from the "category_id" number when first creating the new record

(a different page). I'm now trying to get the EDIT page to work properly - just stumped on this last part.

Does that help?

Sign In

Pulling and updating data from a seperate DB Table

Recommended Posts

spacepoet

Link to comment

Share on other sites

mikesta707

Link to comment

Share on other sites

spacepoet

Link to comment

Share on other sites

mikesta707

Link to comment

Share on other sites

spacepoet

Link to comment

Share on other sites

mikesta707

Link to comment

Share on other sites

spacepoet

Link to comment

Share on other sites

mikesta707

Link to comment

Share on other sites

spacepoet

Link to comment

Share on other sites

mikesta707

Link to comment

Share on other sites

spacepoet

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information