Browse/update a sql database from php

[esoteric]

Hi, sorry for asking so many questions but trying to get the 'admin' part of my site done.

 

I have written a form which i can use to post new products to my database, all works fine. But is there anyway to browse products that are currently in there, like maybe a list or them all below the form which would allow me to see what i have instead of having to go through my web host each time?

 

If the above is possible and could list my products say like;

1 - product 1 - £10

2 - product 2 - £17

etc...

 

Could i then update these somehow?

 

thanks in advance.

[jcbones]

This will get you started:

 

<?php
//include connection info.
include('config.php'); //point to file that holds database connection info.

//function to show all results from a table.
function browseData($tbl) {
$result = mysql_query("SELECT * FROM `$tbl`");
$output = NULL;
$output .= mysql_num_rows($result) . " Results!";
$c = mysql_num_fields($result);
$output .= "<table class=\"search\">";

for ($i=0; $i < $c; $i++) { 
	$output .= '<th style="background-color:blue;">'.ucwords(mysql_field_name($result, $i)).'</th>'; 
}
$d = 1;
while($r = mysql_fetch_array($result)) {
	$output .= '<tr style="background-color:';
	$output .= (($d % 2) == 0) ? '#ACCDE2;' : '#0088E2;' ;
	$output .= '">';
	$i = 0;
	while($i < $c) {
		$output .= "<td>" . $r[$i] . "</td>";
		$i++;
	}

	$output .= "</tr>";
	$d++;
}
$output .= "</table><br/><br/>";
return $output;
}

//call the function, and print the results to the page.
$db_table = 'test'; //Your table name.

echo browseData($db_table);
?>

 

This is an old function that will get the results from a specific table in your database.  You will have to include your database connection file, and edit the $db_table variable.

For editing, I would link the row id to a script that would use the database to populate a form for editing.

[esoteric]

I have the contents being listed now, i just need help on how to update each product, be great if i could someone click on the name and that brings up edit options?

 

this is what i have so far.

if (!mysql_connect($db_host, $db_user, $db_pwd))
    die("Can't connect to database");

if (!mysql_select_db($database))
    die("Can't select database");

$result = mysql_query("SELECT ID, ProductName, ProductId FROM {$table}");
if (!$result) {
    die("Query to show fields from table failed");
}
$fields_num = mysql_num_fields($result);

echo "<h1>Table: {$table}</h1>";
echo "<table border='0'><tr>";
for($i=0; $i<$fields_num; $i++)
{
$field = mysql_fetch_field($result);
echo "<td>{$field->name}</td>";
}
echo "</tr>\n";
while($row = mysql_fetch_row($result))
{
echo "<tr>";
foreach($row as $cell)
echo "<td>$cell</td>";

echo "</tr>\n";
}
mysql_free_result($result);
?>

 

Edit: Sorry we must have posted at the same time. I don't understand your meaning 'link the row id to a script' to be able to edit

[jcbones]

1. Retrieve database info.

2. Link the row's primary key (usually `id`) to another script through a _GET variable.  ie. /index.php?get=id

3. Grab the primary key in the second script, and populate a form.

4. Post the form to a script that will update the database.

[esoteric]

I made a new form which send the inputs to this script

 

<?php

$con = mysql_connect("xxxxx", "xxxxxx", "xxxxxx");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

$ProductImage 		= $_POST['ProductImage'];
$ProductLink 		= $_POST['ProductLink'];
$ProductName 		= $_POST['ProductName'];
$ProductId 			= $_POST['ProductId'];
$Category 			= $_POST['Category'];
$ProductVersion		= $_POST['ProductVersion'];
$ProductDescription = $_POST['ProductDescription'];
$ProductPrice 		= $_POST['ProductPrice'];
$ShippingPrice 		= $_POST['ShippingPrice'];

mysql_select_db("product_catalogue") or die( "Unable to select database");
mysql_query("UPDATE Products SET
(
	ProductImage, 
	ProductLink,
	ProductName, 
	ProductId, 
	Category, 
	ProductVersion, 
	ProductDescription, 
	ProductPrice, 
	ShippingPrice
) 
VALUES 
(
	'".$ProductImage."',
	'".$ProductLink."', 
	'".$ProductName."', 
	'".$ProductId."', 
	'".$Category."', 
	'".$ProductVersion."', 
	'".$ProductDescription."', 
	'".$ProductPrice."', 
	'".$ShippingPrice."'

WHERE ProductId = '".$ProductId."')");

echo " Update complete "; 

mysql_close($con);  
?>

 

But nothing is being updated. Any ideas?

[esoteric]

This is where im at, been staring at this all day and can't get it too work, really appreciate any help.

 

<?php

$con = mysql_connect("XXX");
mysql_select_db("product_catalogue") or die( "Unable to select database");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
$image 				= $_POST['image'];
$link 				= $_POST['link'];
$name 				= $_POST['name'];
$id 				= $_POST['id'];
$cat 				= $_POST['cat'];
$version			= $_POST['version'];
$description 		= $_POST['decription'];
$price 				= $_POST['price'];
$shipping 			= $_POST['shipping'];

if (!$id )
  {
  die('Product ID is required!');
  }
  else
  {
$query = " UPDATE 'Products' 
SET 
'ProductImage' 			= '".$ProductImage."', 
'ProductLink'			= '".$ProductLink."', 
'ProductName'			= '".$ProductLink."', 
'Category' 				= '".$Category."', 
'ProductVersion' 		= '".$ProductVersion."', 
'ProductDescription' 	= '".$ProductDescription."', 
'ProductPrice' 			= '".$ProductPrice."', 
'ShippingPrice' 		= '".$ShippingPrice."' 

WHERE 
'ProductId' = '".$ProductId."' ";

echo " Update complete "; 

mysql_close($con);  
  }
?>

[jcbones]

<?php

$con = mysql_connect("XXX");
mysql_select_db("product_catalogue") or die( "Unable to select database");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
$image 				= $_POST['image'];
$link 				= $_POST['link'];
$name 				= $_POST['name'];
$id 				= $_POST['id'];
$cat 				= $_POST['cat'];
$version			= $_POST['version'];
$description 		= $_POST['decription'];
$price 				= $_POST['price'];
$shipping 			= $_POST['shipping'];

if (!$id )
  {
  die('Product ID is required!');
  }
  else
  {
$query = " UPDATE 'Products' 
SET 
'ProductImage' 			= '$image', 
'ProductLink'			= '$link', 
'ProductName'			= '$name', 
'Category' 				= '$cat', 
'ProductVersion' 		= '$version', 
'ProductDescription' 	= '$description', 
'ProductPrice' 			= '$price', 
'ShippingPrice' 		= '$shipping' 

WHERE 
'ProductId' = '$id' ";

echo " Update complete "; 

mysql_close($con);  
  }
?>