PHP DB Updater [Archive] - CodingForums.com

Scrowler

07-24-2003, 09:23 AM

I'm not sure of the correct code to do this:
Here's what I think would update the table defined in a header string. But I'm not good on authentication, could you also include a login dialog like cPanel's one in the corrected code :):

<html>
<head>
<title>blah</title>
</head>
<body>
<form name="update" method="admin.php">
<? /* admin.php is the same file as this is, smart or not? */ ?>
<textarea content=><? /* code for retrieving info from sql db here */ ?></textarea>
<button type=submit name=submit>
</form>
<?
$location = "localhost";
$username = "username";
$password = "password";
$database = "database";
$column = "columnthatIwannaupdate";

$conn = mysql_connect("$location","$username","$password");
if (!$conn) die ("Could not connect MySQL");
mysql_select_db($database,$conn) or die ("Could not open database");

$query = "UPDATE tablename SET column=$textarea WHERE location=$column";
$result = mysql_query($query);
?>

Will this work? I'm pretty sure I missed a few things. (I know I missed the login dialog, I dunno how to do it) Thanks in advance.

Also I'd like the option of having a combobox that lists all the columns in a database, without having to choose the table, and then when one is chosen, it sets the value of the combobox to $column which is the one to be updated :D

raf

07-24-2003, 10:39 AM

I think you need to look at a tutorial or so.
Setting up a tight login and security system can be quite complex --> it's not only passing the login, you need to check on each page, for each action, if the user is logged in (at pagelevel) and has the right permissions (at page and action level). So this involves loginform, validating against the db, storing the IP and sessionID inside the db, storing the userprofile in a sessionvariable (or in the sessionstable), validiting each user-request against the sessiontable.

Then you've got your update thing. Check out the php site for the mysql-functions (tablelist, fieldlist, field-types)
http://be.php.net/mysql
You then need to run such a function to get a list of tables and fields (which is quite easy, actually --> run mysql_list_tables() and when you loop through the recordset, run mysql_list_fields(). Then print the recordst from this recordset), and fieldtypes, because when you dynamically build the sql-statement, you need to know the column-type (to know if the value needs to be quoted) and the valid values (how long, is it date or datetime or timestamp etc).

So if you read through the functions, and maybe rip some of the code there, it shouldn't be that difficult to get the code you need. But this is all PHP and this forum is also more about helping with codeproblems instead of writing quite extensive pieces of code ... (maybe you'll find someone here or in the PHP-forum that wants to write it for you, but i can only point you in the right direction)

Scrowler

07-24-2003, 09:13 PM

Thanks for that,
but I just want to know wether the script I posted has correct logic and syntax?
--edit--
Anyone? Don't worry about the authentication part just say..
Update $columnname with whats in $textarea..

raf

07-25-2003, 08:40 AM

it doesn't look right.
Your current update, will update the value for a column that is called 'column' in a table that is called 'tablename'.

I suppose, you dynamically wan't to insert the right columnname here, so you'd need
$query = "UPDATE " . $tablename . " SET " . $columnname . "=" .$textarea. " WHERE " . $location . "=" . $column;

Now. 2 problems.
- the condition --> what is location ? a column in your table ? Normally, this should be the name of your primary key-column, and $column should be the primery-key value of the record that need to be updated
- columntype --> when you dynamically build a query like this, you need to know the column-types (see my previous post) location should be numerical (since it's supposed to be the PK) buth the updated variable isn't always numerical --> so you need the columntypes as a hidden formfield, to determine if the values need the be enclosed in quotes and if you need addslashes on them

Scrowler

07-25-2003, 10:03 AM

<html><head><title>Liverpool FC Administration</title>
<link href="css.css" rel="stylesheet" type="text/css">
</head><body>
<?
//$check = $_COOKIE["liverpool"];
//if($check == 'azkazk') {
// database info stuff..
// put this in a seperate file or whatever.. keep it there
$dbhost="localhost";
$dbuser="username"; //your username
$dbpass="***"; //your passworld
$dbname="dbname"; //your database name
$dbconn = mysql_connect($dbhost,$dbuser,$dbpass) or die("Could not connect to the MySQL database.");
$mySQLDB = mysql_select_db($dbname);

// this makes it so that column= will be the $column name in the table
// additionally, table= will be $table
$blah = mysql_query("SELECT ".$id." FROM ".$cat." ORDER BY ".$id." asc");
?>
<p align="center">Welcome to the Liverpool FC Administration area. Hopefully you
know your way around the site, as we haven't got a selector so you can select
which table to edit, so enter the category that you want to edit and enter the
table name eg: www.cerberusgraphics.com/cypher/liverpool/admin.php?cat=players&id=owen</p>
<p align="center">Then hit 'Go' or press enter, make the changes and hit submit.</p>
<div align="center"></div>
<form action="" name="admin">
<div align="center">
<p>
<textarea name="content" cols="80" rows="20" content=><?
if($cat==NULL) {
print "No data selected"; }
else {
while ($rowz=mysql_fetch_array($blah)) {
print "$rowz[$id]" or die ("Unable to access data."); } }
?></textarea>
</p>
<p>
<input type="button" name="Submit" value="Submit Changes" onclick=>
<?php
mysql_query("
UPDATE $cat SET $textarea WHERE $id
") or die ("Could not complete database query.");

print("You have successfully updated: ".$id." in table: ".$cat);
?></input>
</p>
</div>
</form>
<? // } else {
//print "You are not authorised to be here."; } ?>
</body>
</html>
It shows the contents ok, but I can't update it. Help?

raf

07-25-2003, 04:04 PM

You've got:
UPDATE $cat SET $textarea WHERE $id
but you must have
UPDATE $cat SET $column = $textarea WHERE $id

so you need the volumn-name and the datatype of that column (see my previous post)

Scrowler

07-26-2003, 02:42 AM

Thanks

raf

07-26-2003, 05:01 PM

You're welcomme:thumbsup: