Alright I have a MYSQL database with data for Clients. I am displaying this Client info on a HTML Table. I want the last 2 Table Cells to have EDIT and DELETE Functions. I am almost there I just need a final solution to linking them together and getting them to talk.

http://www.daniweb.com/web-development/php/threads/111269 This was a good post but he was getting the URL from MYSQL I just want modify.php and then the id.

Here is my HTML Table Page (Its a WIP)

<html>
    <head>
    <title>Vipre Database</title>
    <meta http-equiv="content-type" content="text/html; charset=uf-8" />
    <style type="text/css">
        lable { display: block; }
    </style>
    <script src="sorttable.js"></script>
</head>
<body>
<a href="addclient.php">Add Client</a>
<?php

include_once 'resources/init.php';

$query="SELECT * FROM Client";
$result=mysql_query($query);

mysql_close();
?>

<?php
//Table starting tag and header cells
echo "<table class='sortable' border='1' cellspacing='5' cellpadding='5'><tr><th>First Name</th><th>Last Name</th><th>Email</th><th>Invoice #</th><th>Windows Key</th><th>Windows Type</th><th>VIPRE Type</th><th>User Count</th><th>Year Count</th><th>Start Date</th><th>Expire Date</th><th>Vipre Key</th><th>Edit Client</th></tr>";
    while($row = mysql_fetch_array($result)){
        //Display the results in differnt Cells
        echo "<tr><td>" . $row['firstname'] . "</th><td>" . $row['lastname'] . "</td><td>" . $row['email'] . "</td><td>" . $row['invoice'] . "</td><td>" . $row['wink'] . "</td><td>" . $row['wint'] . "</td><td>" . $row['vtype'] . "</td><td>" . $row['usera'] . "</td><td>" . $row['yeara'] . "</td><td>" . $row['sdate'] . "</td><td>" . $row['edate'] . "</td><td>" . $row['viprek'] . "</td><td><a href=\"modify.php?id=" . $row['id'] . "Edit</a>" . "</td></tr>";
    
}
echo "</table>";
?>





</body>
</html>

Here is my Edit Page

<?php

include 'init.php';
if (isset ($_POST['submit'])) {
    $q = "SELECT * FROM Client WHERE id = $_GET[id]";
    $result = mysql_query($q);
    $client = mysql_fetch_array($result);
    }
?>
    <a href="index.php">Back To List</a>
  
<form action="insert.php" method="post">
First Name: <input type="text" name="firstname" value="<?php echo $client['firstname']; ?>" />
Last Name: <input type="text" name="lastname" />
<br>
<br>
Email: <input type="text" name="email" />
<br>
<br>
Invoice #:<input type="text" name="invoice" maxlength="5" size="5" />
<br>
<br>
<br>
<br>
Windows Key:<input type="text" name="wink" maxlength="24" size="24" />
Windows Type:<input type="text" name="wint" maxlength="24" size="24" />
<br>
<br>
<br>
<br>
VIPRE Type:<input type="text" name="vtype" maxlength="3" size="3" />
User Count:<input type="text" name="usera" maxlength="3" size="3" />
Year Count:<input type="text" name="yeara" maxlength="3" size="3" />
<br>
<br>
Start Date:<input type="text" name="sdate" maxlength="10" size="10" />
Expire Date:<input type="text" name="edate" maxlength="10" size="10" />
<br>
<br>
VIPRE Key: <input type="text" name="viprek" maxlength="24" size="24" />
<br>
<br>
<br>
<input type="hidden" name="id" value="<?php echo $_GET['id']; ?>" />
<input type="submit" />
</form>
    
    <?php
    if(isset($_POST["submit"])) {
        $u = "UPDATE Client SET `firstname`='$_POST[firstname]' WHERE id = $_POST[id]";
        mysql_query($u) or die (mysql_error());
        
        echo "User has been modified!";
        header("Location: index.php");
        
    } else {

        
    }
    ?>
Member Avatar for diafol

You HAVE to clean your input (GET).
Could anybody get hold of these pages or are they strictly admin-only pages - in other words are they protected. Is the allowed user allowed to edit any user in the list?

You HAVE to clean your input (GET).
Could anybody get hold of these pages or are they strictly admin-only pages - in other words are they protected. Is the allowed user allowed to edit any user in the list?

Before I start I have been working on my code a bit.

They are public right now because I am to lazy to make them private but they will be hosted locally when I get this setup at work on our server.

You will have to excuse my lack of knowledge about PHP.

Sorry for double post.

Alright I fixed some typing issue I miss typed.

index

<html>
    <head>
    <title>Vipre Database</title>
    <meta http-equiv="content-type" content="text/html; charset=uf-8" />
    <style type="text/css">
        lable { display: block; }
    </style>
    <script src="sorttable.js"></script>
</head>
<body>
<a href="addclient.php">Add Client</a>
<?php

include_once 'resources/init.php';

$query="SELECT * FROM Client";
$result=mysql_query($query);

mysql_close();
?>

<?php
//Table starting tag and header cells
echo "<table class='sortable' border='1' cellspacing='5' cellpadding='5'><tr><th>First Name</th><th>Last Name</th><th>Email</th><th>Invoice #</th><th>Windows Key</th><th>Windows Type</th><th>VIPRE Type</th><th>User Count</th><th>Year Count</th><th>Start Date</th><th>Expire Date</th><th>Vipre Key</th><th>Edit Client</th></tr>";
    while($row = mysql_fetch_array($result)){
        //Display the results in differnt Cells
        echo "<tr><td>" . $row['firstname'] . "</th>
            <td>" . $row['lastname'] . "</td>
            <td>" . $row['email'] . "</td>
            <td>" . $row['invoice'] . "</td>
            <td>" . $row['wink'] . "</td>
            <td>" . $row['wint'] . "</td>
            <td>" . $row['vtype'] . "</td>
            <td>" . $row['usera'] . "</td>
            <td>" . $row['yeara'] . "</td>
            <td>" . $row['sdate'] . "</td>
            <td>" . $row['edate'] . "</td>
            <td>" . $row['viprek'] . "</td>
                                                        
            <td><a href=\"modify.php?id=" . $row['id'] . "\">Edit</a>" . "</td></tr>";
    
}
echo "</table>";
?>





</body>
</html>

Edit

<?php

include 'resources/init.php';
if (isset ($_POST['submit'])) {
    $q = "SELECT * FROM Client WHERE id = $_GET[id]";
    $result = mysql_query($q);
    $client = mysql_fetch_array($result);
    }
?>
    <a href="index.php">Back To List</a>
  
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
First Name: <input type="text" name="firstname" value="<?php echo $client['firstname']; ?>" />
Last Name: <input type="text" name="lastname" value="<?php echo $client['lastname']; ?>" />
<br>
<br>
Email: <input type="text" name="email" value="<?php echo $client['email']; ?>" />
<br>
<br>
Invoice #:<input type="text" name="invoice" maxlength="5" size="5" value="<?php echo $client['invoice']; ?>" />
<br>
<br>
<br>
<br>
Windows Key:<input type="text" name="wink" maxlength="24" size="24" value="<?php echo $client['wink']; ?>" />
Windows Type:<input type="text" name="wint" maxlength="24" size="24" value="<?php echo $client['wint']; ?>" />
<br>
<br>
<br>
<br>
VIPRE Type:<input type="text" name="vtype" maxlength="3" size="3" value="<?php echo $client['vtype']; ?>" />
User Count:<input type="text" name="usera" maxlength="3" size="3" value="<?php echo $client['usera']; ?>" />
Year Count:<input type="text" name="yeara" maxlength="3" size="3" value="<?php echo $client['yeara']; ?>" />
<br>
<br>
Start Date:<input type="text" name="sdate" maxlength="10" size="10" value="<?php echo $client['sdate']; ?>" />
Expire Date:<input type="text" name="edate" maxlength="10" size="10" value="<?php echo $client['edate']; ?>" />
<br>
<br>
VIPRE Key: <input type="text" name="viprek" maxlength="24" size="24" value="<?php echo $client['viprek']; ?>" />
<br>
<br>
<br>
<input type="hidden" name="id" value="<?php echo $_GET['id']; ?>" value="<?php echo $client['id']; ?>" />
<input type="submit" />
</form>
    
    <?php
    if(isset($_POST["submit"])) {
        $u = "UPDATE Client SET `firstname`='$_POST[firstname]', `lastname`='$_POST[lastname]', `email`='$_POST[email]', `invoice`='$_POST[invoice]', `wink`='$_POST[wink]', `wint`='$_POST[wint]', `vtype`='$_POST[vtype]', `usera`='$_POST[usera]', `yeara`='$_POST[yeara]', `sdate`='$_POST[sdate]', `edate`='$_POST[edate]', `viprek`='$_POST[viprek]',  WHERE id = $_POST[id]";
        mysql_query($u) or die (mysql_error());
        
        echo "User has been modified!";
        header("Location: index.php");
        
    } else {

        
    }
    ?>

I am reading about cleaning... but maybe you could tell me how?

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = 4' at line 1

Alright well I got the data passed through to the next page! YAY.. but now I have another issue.

Member Avatar for diafol

Hold on. Try getting security right before you start, otherwise you'll be rewriting your code all over the place.

You use querystring parameters (url) to pass unsecured data - this must be challenged, validated and verified before you put it anywhere near a DB.

e.g.

//in your config inlcude file
$salt1 = 'mysaltysalt';
$salt2 = 'anamazingsalt0fth32nd0rd3r';
...
//checking script
if(isset($_GET['id']) && is_int($_GET['id']) && isset($_GET['conf']) && $_GET['conf'] == md5($salt1 . $_GET['id'] . $salt2)){
  //do your stuff
}else{
  //tell 'em to go away
} 

...your link builder:
$id = $row['id'];
$conf = md5($salt1 . $id . $salt2);

..."<td><a href=\"modify.php?id=$id&conf=$conf</a></td>"...

That should stop malicious deletions / edits from outside. However, you need to protect these pages with login/session data.
Page protection is the very minimum that you should be using.

Well I don't think it needs extra code to be secure... I work with only 2 other people at my work. This will be hosted on our server at work. Its not on the internet...

Thanks for info though. I still have a Syntax error. I will work this code you provided in my code if I can. Just need to read it to learn where it goes.

Member Avatar for diafol

OK, no prob. Is the error on line 5:

$q = "SELECT * FROM Client WHERE id = $_GET[id]";

Are you sure that the field is actually called 'id'?

Member Avatar for diafol
$q = "SELECT * FROM Client WHERE id = $_GET[id]";
echo $q;

Put an echo and copy the text from the screen. Paste it into phpmyadmin SQL box and run it - see what happens.

SELECT * FROM Client WHERE id = 2 Well this printed because I selected entry 2.

I ran it in phpmyadmin and it selected id 2

Is there an issue... I would have expected this to happen but...

Member Avatar for diafol

Yes, I expected it to work too, just obviating some DB query problems. I assume that you are connected to the correct DB in the php script. I must admit I'm a little lost.
I can't see why you'd get an error on a WHERE clause if the fieldname is correct and the value is innocuous.

??EDIT

Why is there a post/submit around the first sql query?
I thought that this was triggered in response to the presence of $_GET.
The submit thing isn't supposed to kick in unless the form on that page is sent right?
BUT the data in the form should be shown via $_GET FROM A LINK on the previous page.

I am sorry I'm not sure I follow :(

Member Avatar for diafol
include 'resources/init.php';
if (isset ($_POST['submit'])) {
    $q = "SELECT * FROM Client WHERE id = $_GET[id]";
    $result = mysql_query($q);
    $client = mysql_fetch_array($result);
    }
?>

I can't see how this would run when you land on that page as you need the form to be sent in order to run the query.

include 'resources/init.php';
if (isset ($_GET['id'])) {
    $q = "SELECT * FROM Client WHERE id = $_GET['id']";
    $result = mysql_query($q);
    $client = mysql_fetch_array($result);
    }
?>

Doesn't that make more sense? That way you get all the values into the form on first page load as well.

Hmm your code has a syntax issue... looking into why... :\

= $_GET"; The ' ' was the issue.. But I still have the SAME issue. :(

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = 2' at line 1"

Member Avatar for diafol

DOH!

like this:

$q = "SELECT * FROM Client WHERE id = {$_GET['id']}";

That seems to have been the trouble all along maybe?!

But anyway, you shouldn't put an unsanitized value into sql.
You should use mysql_real_escape_query() or for this intval():

$id = intval($_GET['id']);

$q = "SELECT * FROM Client WHERE id = $id";

DOH!

like this:

$q = "SELECT * FROM Client WHERE id = {$_GET['id']}";

That seems to have been the trouble all along maybe?!

But anyway, you shouldn't put an unsanitized value into sql.
You should use mysql_real_escape_query() or for this intval():

$id = intval($_GET['id']);

$q = "SELECT * FROM Client WHERE id = $id";

well I sill have the issue.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = 2' at line 15

different error now.. hmm looking at line 15.

FROM Client WHERE id = {$_GET}";

Opps posted wrong code...

Here is my Code as it is now.

<?php

include 'resources/init.php';
if (isset ($_GET['id'])) {
    $q = "SELECT * FROM Client WHERE id = {$_GET['id']}";
    $result = mysql_query($q);
    $client = mysql_fetch_array($result);
    }
?>
    <a href="index.php">Back To List</a>
  
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
First Name: <input type="text" name="firstname" value="<?php echo $client['firstname']; ?>" />
Last Name: <input type="text" name="lastname" value="<?php echo $client['lastname']; ?>" />
<br>
<br>
Email: <input type="text" name="email" value="<?php echo $client['email']; ?>" />
<br>
<br>
Invoice #:<input type="text" name="invoice" maxlength="5" size="5" value="<?php echo $client['invoice']; ?>" />
<br>
<br>
<br>
<br>
Windows Key:<input type="text" name="wink" maxlength="24" size="24" value="<?php echo $client['wink']; ?>" />
Windows Type:<input type="text" name="wint" maxlength="24" size="24" value="<?php echo $client['wint']; ?>" />
<br>
<br>
<br>
<br>
VIPRE Type:<input type="text" name="vtype" maxlength="3" size="3" value="<?php echo $client['vtype']; ?>" />
User Count:<input type="text" name="usera" maxlength="3" size="3" value="<?php echo $client['usera']; ?>" />
Year Count:<input type="text" name="yeara" maxlength="3" size="3" value="<?php echo $client['yeara']; ?>" />
<br>
<br>
Start Date:<input type="text" name="sdate" maxlength="10" size="10" value="<?php echo $client['sdate']; ?>" />
Expire Date:<input type="text" name="edate" maxlength="10" size="10" value="<?php echo $client['edate']; ?>" />
<br>
<br>
VIPRE Key: <input type="text" name="viprek" maxlength="24" size="24" value="<?php echo $client['viprek']; ?>" />
<br>
<br>
<br>
<input type="hidden" name="id" value="<?php echo $_GET['id']; ?>" />
<input type="submit" name="submit" Value="Modify" />
</form>
    
    <?php
    if(isset($_POST["submit"])) {
        $u = "UPDATE Client SET
        
        `firstname`='$_POST[firstname]',
        `lastname`='$_POST[lastname]',
        `email`='$_POST[email]',
        `invoice`='$_POST[invoice]',
        `wink`='$_POST[wink]',
        `wint`='$_POST[wint]',
        `vtype`='$_POST[vtype]',
        `usera`='$_POST[usera]',
        `yeara`='$_POST[yeara]',
        `sdate`='$_POST[sdate]',
        `edate`='$_POST[edate]',
        `viprek`='$_POST[viprek]', WHERE id = $_POST[id]";
        
        mysql_query($u) or die (mysql_error());
        
        echo "User has been modified!";
        header("Location: index.php");
Member Avatar for diafol

line 15? doesn't make sense to me.
Off to bed. Perhaps somebody else will help you in the meantime. Good luck.

Alright thanks for help.

Because my issue has changed so much since my first post. /thread

Line 63, remove the comma before the WHERE (also answered in your other thread). Please do not repeat questions in new threads.

Member Avatar for diafol
if(isset($_POST["submit"])) {
        $u = "UPDATE Client SET
 
        `firstname`='$_POST[firstname]',
        `lastname`='$_POST[lastname]',
        `email`='$_POST[email]',
        `invoice`='$_POST[invoice]',
        `wink`='$_POST[wink]',
        `wint`='$_POST[wint]',
        `vtype`='$_POST[vtype]',
        `usera`='$_POST[usera]',
        `yeara`='$_POST[yeara]',
        `sdate`='$_POST[sdate]',
        `edate`='$_POST[edate]',
        `viprek`='$_POST[viprek]', WHERE id = $_POST[id]";

Yes there is an error here as Pritaeas states. I sincerely hope that this was not the error that you were referring to all along. But it sounds as if it was. :(

After changing those things I know have this error.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 14

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.