help please :) quick question

0x343230 · March 26, 2013

hello there,

I am relatively new to PHP/SQL and am building my second project, a mini CRM.

Inside the CRM is a form to add a "sale" (i'm a telemarketer)

that form posts to DB

another feature is a "sales tracker" which outputs the db table into an HTML table

One thing I'm looking to add is the ability to modify a certain field of a certain row.

the rows are as follows:

contact name, contact number, date appointment was scheduled, and status of the appointment

when I fill out the form to add a new "sale", the status by default is "unknown" until I receive an update from the manager.

after I get the update on the status, I wish to "update" the field right on the PHP/SQL generated table, rather than accessing the DB via phpmyadmin.

Here is the code (copied from somebody else on the internet) used to generate the table, if this helps.

(edit: I know it's probably as easy as an update command, but since the table is dynamically generated I don't know how to go about it.

<?php
$global_dbh = mysql_connect("localhost", "root", "")
or die("Could not connect to database");
mysql_select_db("cbdb", $global_dbh)
or die("Could not select database");
function display_db_query($query_string, $connection, $header_bool, $table_params) {
// perform the database query
$result_id = mysql_query($query_string, $connection)
or die("display_db_query:" . mysql_error());
// find out the number of columns in result
$column_count = mysql_num_fields($result_id)
or die("display_db_query:" . mysql_error());
// Here the table attributes from the $table_params variable are added
print("<TABLE $table_params >\n");
// optionally print a bold header at top of table
if($header_bool) {
print("<TR>");
for($column_num = 0; $column_num < $column_count; $column_num++) {
$field_name = mysql_field_name($result_id, $column_num);
print("<TH>$field_name</TH>");
}
print("</TR>\n");
}
// print the body of the table
while($row = mysql_fetch_row($result_id)) {
print("<TR ALIGN=LEFT VALIGN=TOP>");
for($column_num = 0; $column_num < $column_count; $column_num++) {
print("<TD>$row[$column_num]</TD>\n");
}
print("</TR>\n");
}
print("</TABLE>\n"); 
}

function display_db_table($tablename, $connection, $header_bool, $table_params) {
$query_string = "SELECT * FROM $tablename";
display_db_query($query_string, $connection,
$header_bool, $table_params);
}
?>
<HTML><HEAD><TITLE>Sales</TITLE></HEAD>
<BODY>
<TABLE><TR><TD>
<?php
//In this example the table name to be displayed is  static, but it could be taken from a form
$table = "sales";

display_db_table($table, $global_dbh,
TRUE, "border='2'");
?>
</TD></TR></TABLE></BODY></HTML>

Edited March 26, 2013 by 0x343230

fesan · March 26, 2013

You need a form to update fields without phpmyadmin.

I'm very pro outputting my data directly into a form and change it on the fly.

I tend to CSS the form so it does not look like an traditional form. Alternatively you could use Java/AJAX.

example of code:

$dbhost = "yourhost";
$dbusr = "yourusername";
$dbpass = "yourpassword";
$db = "yourbatabase";
$dbtable = "yourtable";


mysql_connect ($dbhost, $dbusr, $dbpass) || 
die("Could not connect to the server.");


mysql_select_db($db) || 
die("Could not find the Data Base");


$conn = mysql_connect($dbhost, $dbusr, $dbpass);
mysql_select_db($dbtable, $conn);
$query = "SELECT * FROM $dbtable"; 
$result = mysql_query($query) or die(mysql_error()); 
while($row = mysql_fetch_array($result)){ 
echo "<form id='form1' name='form1' action='yourprocesspage.php&id=".$row['id']."' method='POST'>\r\n"; 
echo "<input type='text' name='yourfield' id='yourfield' size='6' value='". $row['your_DB_row']."' />\r\n";
echo "<input type='submit' name='submit' id='submit' value='Submit' />\r\n"; 
echo "</form>\r\n";

you would then need to process the data sent to the process page:

//get the variables and making sure they are not empty
$yourfield = !empty($_POST ['yourfield']) ? $_POST['yourfield'] : '';

$dbhost = "yourhost";
$dbusr = "yourusername";
$dbpass = "yourpassword";
$db = "yourbatabase";
$dbtable = "yourtable";

mysql_connect ($dbhost, $dbusr, $dbpass) ||  
die("Could not connect to the server."); 

mysql_select_db($db) ||  
die("Could not find the Data Base"); 

$conn = mysql_connect($dbhost, $dbusr, $dbpass); 
mysql_select_db($dbtable, $conn); 

$sql = "UPDATE $dbtable SET 
your_DB_row = '$yourfield',  
WHERE id = '$_GET[id]'"; //we sent the row id with the adressbar and are retreiveing it via $_GET[''] 

$result = mysql_query($sql, $conn) or die (mysql_error());

Edited March 26, 2013 by fesan

0x343230 · March 26, 2013

I'm a little confused. First I got an unexpected $end error, so I looked it up and I think you forgot to close the while loop with a } so i added one at the end and it returns a blank screen. here is the code:

<?php

$dbhost = "localhost";
$dbusr = "root";
$dbpass = "";
$db = "cbdb";
$dbtable = "sales";


mysql_connect ($dbhost, $dbusr, $dbpass) || 
die("Could not connect to the server.");


mysql_select_db($db) || 
die("Could not find the Data Base");


$conn = mysql_connect($dbhost, $dbusr, $dbpass);
mysql_select_db($dbtable, $conn);
$query = "SELECT * FROM $dbtable";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){ echo "<form id='form1' name='form1' action='proc.php&id=".$row['id']."' method='POST'>\r\n";
echo "<input type='text' name='yourfield' id='yourfield' size='6' value='". $row['status']."' />\r\n";
echo "<input type='submit' name='submit' id='submit' value='Submit' />\r\n"; echo "</form>\r\n"; 

}
?>

<?php

//get the variables and making sure they are not empty
$yourfield = !empty($_POST ['status']) ? $_POST['status'] : '';
$dbhost = "localhost";
$dbusr = "root";
$dbpass = "";
$db = "cbdb";
$dbtable = "sales";mysql_connect ($dbhost, $dbusr, $dbpass) ||  die("Could not connect to the server."); mysql_select_db($db) ||  die("Could not find the Data Base"); $conn = mysql_connect($dbhost, $dbusr, $dbpass); mysql_select_db($dbtable, $conn); $sql = "UPDATE $dbtable SET status = '$yourfield',  WHERE id = '$_GET[id]'"; //we sent the row id with the adressbar and are retreiveing it via $_GET[''] $result = mysql_query($sql, $conn) or die (mysql_error());  

?>

two seperate files, first one is updateform.php second one is proc.php

did I do this wrong?

fesan · March 26, 2013

sorry for the missing bracket!

what page turns out blank? the first or the second?

The second page "proc.php" has no code after the row update.

you could use a header to get back to the previous page header("Location: http://sitename");

Or you could just an a link:

echo "<a href='updateform.php'>back...</a>

Barand · March 26, 2013

remove the second mysql_connect and mysql_select_db.

You only need the one and the second select_db is wrong - it selects the table and not the database

0x343230 · March 26, 2013

oh no problem, I greatly appreciate the help!

I think I'm doing something wrong.

First, I don't have an "ID" row in the table for entries, do I need one?

Second, how am I actually "choosing" the row to update? Is a form supposed to appear?

fesan · March 26, 2013

you are so absolutely right Barand, but i actually have it in a code of mine here and it works just fine!

It doesn't make it more right though!

no, you don't have to have an ID column, but as far as i know it is good practice.

It is a way of separating all the rows from each other with an unique id. If set to unique and auto increment.

Unless you have some other unique column that makes us able to choose that particular row?

The while function should print out every row in your DB and separate them by printing the row ID(or other sort of ID) in the action"" field of the <form>.

After getting this to work you could choose to just print one row, or CSS it to something more view able.

0x343230 · March 26, 2013

I already have a page that prints everything from the DB. Preferably I'd like to just be able to modify the table on the tracker page (the page that prints the DB)

Please see this picture:

http://s9.postimg.org/dwcvphmf3/Untitled.png

See where it says "status" ??

By default, the status is unknown (or "pending"). But I would like to have a dropdown menu display for every single entry in the "status" column which I can update right on the tracker page by clicking "Submit"

Edited March 26, 2013 by 0x343230

0x343230 · March 26, 2013

by adding the "id" column and auto increment, I now get a small form to populate.

Upon submitting the form, I get a URL Not found error.

fesan · March 26, 2013

yea, that is just what the code should do. It is snipped from a page of mine that does the same thing:

http://www.bahrawy.net/dev/cal_snip.PNG

on the picture every green field is a input field where i can write directly into the table. hit enter or the button at the end saying "Lagre" and it saves.

The output of your first code should go straight to a <form> with a <select> input.

add an extra column to your HTML table to get the submit button.

the URL not found seems like you have added the header code and not updated to the correct path of your page?

0x343230 · March 26, 2013

I'm super confused... Killing my brain trying to figure this out....gah!

Here is the group of files used in my mini CRM if you don't mind checking it out!

http://www.fileconvoy.com/dfl.php?id=gf6bb61c8a9f06f08999253154fad659b69a089287

fesan · March 26, 2013

create a completely new file and see if this could help you a bit:(i have not tested the code)

<?php
$status = !empty($_POST ['status']) ? $_POST['status'] : '';

if($_GET['task'] == 'edit') {
$dbhost = "";
$dbusr = "";
$dbpass = "";
$db = "";
$dbtable = "";

$conn = mysql_connect($dbhost, $dbusr, $dbpass);
mysql_select_db($dbtable, $conn);

$sql = "UPDATE ${dbtable} SET
status = '$staus' 
WHERE id = '$_GET[id]'";

$result = mysql_query($sql, $conn) or die (mysql_error());
echo "Success";
} else {echo "Trouble";}

?>

<table>
  <tr>
    <td><strong>ID</strong></td>
    <td><strong>Contact Name</strong></td>
    <td><strong>Contact Number</strong></td>
    <td><strong>Contact second number</strong></td>
    <td><strong>Date Set</strong></td>
    <td><strong>Notes</strong></td>
    <td><strong>Status</strong></td>
    <td><strong>Save</strong></td>
  </tr>

<?php
$dbhost = "";
$dbusr = "";
$dbpass = "";
$db = "";
$dbtable = "";

$conn = mysql_connect($dbhost, $dbusr, $dbpass);
mysql_select_db($dbtable, $conn);
$query = "SELECT * FROM $dbtable";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
echo "<tr>\r\n";
echo "<form id='form1' name='form1' action='".$_SERVER['PHP_SELF']."?task=edit&id=".$row['id']."' method='POST'>\r\n";
echo "<td>".$row['id']."</td>\r\n";
echo "<td>".$row['contact_name']."</td>\r\n";
echo "<td>".$row['contact_number_primary']."</td>\r\n";
echo "<td>".$row['contact_number_secondary']."</td>\r\n";
echo "<td>".$row['date_set']."</td>\r\n";
echo "<td>".$row['notes']."</td>\r\n";
echo "<td><select name='status' id='status'>
<option selected value='".$row['status']."' >". $row['status']."</option>
        <option value='1'>1</option>
        <option value='2'>2</option>
        <option value='3'>3</option>
</select></td>\r\n";
echo "<td><input type='submit' name='submit' id='submit' value='Save' /></td>\r\n";
echo "</tr></table>\r\n";
}
?>

was a bit quick there!

Edited March 26, 2013 by fesan

Barand · March 26, 2013

$conn = mysql_connect($dbhost, $dbusr, $dbpass);

mysql_select_db($dbtable, $conn);

The function mysql_select_db() is to select the database. Why do you you persist in passing it a table name?

Edited March 26, 2013 by Barand

fesan · March 27, 2013

I really don´t have a good answer for that!

When i first started with PHP i got some code from another forum and i guess some of it is still left and in very much use unfortunately!

Could i ask what the correct syntax would be?

I guess it is time to convert to mysqli soon as well?

Barand · March 27, 2013

mysql_select_db

0x343230 · March 27, 2013

This is the first real "stump" I've had in PHP, everything else has been a breeze.

I'm going to study a bit more and then come back to this later today when I have it figured out

Thank you for the help everyone!

Sign In

help please :) quick question

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information