Page 1 of 1

Writing variables to table?

Posted: Thu May 17, 2007 7:24 pm
by patrick24
Hi, I'm new to php, and I've been doing online tutorials/manuals to try and understand how to use it. So, I'm just beginning to get a grip on basic php. I've finally figured out how to start using a database and creating tables. I know how to input form data into a table, but I can't seem to figure out how to write a variable from the code into a table, like automatically inserting a date when form is submitted. Maybe I'm going about this the wrong way, and this is probably a dumb question. But as I said, I'm a noob. :oops: So, any help would be much appreciated. Thankyou.

Posted: Thu May 17, 2007 7:44 pm
by bdlang
Do you have a specific bit of code or SQL you're having trouble with?

What database are you working with (MySQL)?

Most likely you can use an auto-update TIMESTAMP field or similar to automatically update the time when you update the table, if you're interested in using that. Otherwise, you can easily do it manually in your query.

Ok, a VERY basic PHP / MySQL insert from a form:
(untested, unremarkable example with pseudocode elements)

Code: Select all

<?php
if ( FORM WAS POSTED ) {
    // connect to MySQL server, select database, all that good stuff here
    //....
    
    // process form data
    if ( !empty($_POST['somedata']) && ctype_alnum($_POST['somedata']) ) {
        // further validation here if you are so inclined
        // at least use m_r_e_s() to escape the data properly
        $cleanData= mysql_real_escape_string($_POST['somedata']);
        // fashion an SQL statement using the now clean data, and NOW() to insert the time
        $sql= "INSERT INTO myTable (data_column, timestamp_column) VALUES ( '{$cleanData}', NOW() )";
        // execute the query
        $result= mysql_query($sql)
            OR die( 'Query error:<br />' .$sql. '<br />' .mysql_error() );
        // check to make sure a record was inserted
        if ( mysql_affected_rows() == 1 ) {
            echo '<p>Successfully entered data.</p>';
        }
    }

} else {
    // show form
?>
<form name="testForm" method=POST action="this.php">
<input name="somedata" type="text" />
<input name="submitTestForm" type="submit" />
</form>
<?php } ?>
There are at least dozen ways the above can be handled, using a parameterized query string, using an auto-updated TIMESTAMP type column, creating a UNIX timestamp, etc ad nauseum. Pick a way that makes sense to you and make it work.

Re: Writing variables to table?

Posted: Thu May 17, 2007 8:01 pm
by califdon
patrick24 wrote:Hi, I'm new to php, and I've been doing online tutorials/manuals to try and understand how to use it. So, I'm just beginning to get a grip on basic php. I've finally figured out how to start using a database and creating tables. I know how to input form data into a table, but I can't seem to figure out how to write a variable from the code into a table, like automatically inserting a date when form is submitted. Maybe I'm going about this the wrong way, and this is probably a dumb question. But as I said, I'm a noob. :oops: So, any help would be much appreciated. Thankyou.
If you have the value in a $variable, all you have to do is put the $variable in the syntax of an INSERT statement, just like the $variables from a form.

Code: Select all

$sql = "INSERT INTO tablename SET `dt_of_entry` = $dt";
or

Code: Select all

$sql = "INSERT INTO tablename ('last_name','first_name','dt_of_entry') VALUES (' " . $_POST['Lname'] . " ',' " . $_POST['Fname'] . " ','$dt')";
If your date $variable is in datetime format, you won't need the single quotes around it.

Posted: Thu May 17, 2007 8:30 pm
by patrick24
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Thanks for the help guys, but I should have posted an example of what I'm doing. Here is what I'm using for the form:

Code: Select all

<form action="<?=$_SERVER['PHP_SELF']?>" method="post">
    Customer: <select name="customer">
<option value="NormA">Arbec, Norm
<option value="SteveB">Barney, Steve
<option value="JamieB">Barney, Jamie
<option value="DarrylB">Berg, Darryl
</select> 
    Drink: <select name="drink">
<option value="Coffee_12oz">Coffee 12oz.
<option value="Coffee_8oz">Coffee 8oz.
<option value="Tea">Tea
<option value="EmergenC">EmergenC
<option value="Americano">Americano
<option value="Latte">Latte
<option value="Breve">Breve
<option value="Mocha">Mocha
</select> 
    Credit: <input name="credit" type="text" value="0">
    <input type="submit" name="submit">
    </form>
Then I want to insert the price for the drink into the table automatically, as well as the date:

Code: Select all

if ($drink == Coffee_12oz) {
    $price = '1.00';
    }
elseif ($drink == Coffee_8oz) {
    $price = '0.75';
    }
elseif ($drink == Tea) {
    $price = '0.50';
    }

......

   $customer = empty($_POST['customer']) ? die ("ERROR: Select a customer") :                                                         mysql_escape_string($_POST['customer']); $drink = empty($_POST['drink']) ? die ("ERROR: Select a drink") : mysql_escape_string($_POST['drink']);
   $credit = mysql_escape_string($_POST['credit']);
   $date = date(m-d-y);

.......

$query = "INSERT INTO $customer (date, drink, credit, price, balance) VALUES ('$date', '$drink', '$credit', '$price', '$balance')";
The drink and credit submits fine into the table, but the price and date columns always come up empty.
Also, I need a way to select the previous balance in the table, so I automatically do the math for the balance after price.
Thanks again.



feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Thu May 17, 2007 9:22 pm
by bdlang
1) PHP date() syntax - your call to date() is wrong, so the value is empty.

2) Your if-else statements are performing comparisons against non-existant constants, e.g. Coffee_12oz rather than string literals, e.g. 'Coffee_12oz'. Your $price variable is empty. You must have E_NOTICE errors turned off. If you must do this, I'd recommend using switch() rather than multiple if-else blocks.

3) Your price is a FLOAT / DECIMAL value. What data type is your `price` column? When dealing with FLOAT values, don't surround them with quotes as you would a string type column.

I would strongly suggest refactoring your code / table. You shouldn't be doing all this data manipulation to decide how much the price is, that should be stored in a table someplace, e.g.

Code: Select all

CREATE TABLE IF NOT EXISTS `drinks` (
drinkID TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
drinkName VARCHAR(25) NOT NULL,
drinkPrice DECIMAL(5,2) NOT NULL,
PRIMARY KEY (drinkID),
UNIQUE (drinkName)
);
All the drink names / pricing is stored in this table and you don't have it hard-coded in your script. If you want to update pricing across all the PHP scripts, you simply update it in one place. Your form pulls down the drinkID / drinkName key / value pairs and when the form is submitted you simply store that drinkID value in your customer data table. Speaking of which.... your customer table should also be refactored into at least two other tables, `customers` and `customerData` with a `customerID` key relating the two. Obviously this is new to you and you won't get it all the first time around.

Some light reading:
MySQL Manual : Tutorial : Using Foreign Keys

Introduction to Database Normalization

Posted: Thu May 17, 2007 10:00 pm
by patrick24
Thanks for your help bdlang. Yes, I don't really know what I'm doing, so I'll do some of that reading. Also I think I need to get use to more basic stuff before tackling what I'm trying to do. I just had an idea, and I thought I could pull it off without really knowing what to do, but I guess there's alot more to learn than I thought.
Thanks.

Posted: Thu May 17, 2007 10:10 pm
by bdlang
Oh, not a problem. I hope my suggestions didn't discourage you; after all, it's your application and whatever design that makes sense to you and works is fine. You will eventually find limitations to your design as you learn, and want to make those changes.

Posted: Fri May 18, 2007 7:52 pm
by patrick24
Thanks, bdlang. Maybe a little frustrasted at times but not discouraged. :) I've only started learning php since this monday so I think I'm doing good for a noob, I can just get carried away sometimes lol. So for now I'm pulling my self back and learning more basic stuff, but I still haven't given up on my database experiment. I actually got everything working but I still have one more question:

Code: Select all

$query = "SELECT balance FROM $customer WHERE id=(SELECT MAX(id))";
	
	$lastbalance = mysql_query($query) or die ("Error in query: $query. ".mysql_error());
	
	$balance = ($lastbalance - $price + $credit);
I'm trying to select the last balance entry in the table, but every time it comes up as '3' so the $price is subtracted from 3.
Image
[/img]

Posted: Fri May 18, 2007 10:39 pm
by bdlang
Ok, the first thing you should understand is that $lastbalance isn't a value returned from the database. It's either a MySQL Resource ID value or FALSE. You have to take that and retrieve a value from it using one of the functions (mysql_result(), mysql_fetch_row(), etc). In this case since you're retrieving a single value I'd suggest mysql_result(), e.g.

Code: Select all

$result= mysql_query($query) OR die( 'Error in query:<br />' .$query. '<br />' .mysql_error() );
$lastbalance= mysql_result($result, 0);

$balance= $lastbalance - ( $price + $credit );
I'm guessing here this is the calculation you want; don't forget about operator precedence!

I won't second guess your query, but make certain you test that in MySQL prior to trying to manipulate the data returned in PHP.

Posted: Fri May 18, 2007 11:54 pm
by patrick24
Ok, that helped a bunch, and I thought it was working but now $lastbalance is 1.25 ?!?
Just a sec, I'm trying to figure what I'm doing here...

Posted: Sat May 19, 2007 12:10 am
by patrick24

Code: Select all

$result= mysql_query($query) OR die( 'Error in query:<br />' .$query. '<br />' .mysql_error() ); 
	
$lastbalance = mysql_result($result, 5);
	
$balance = $lastbalance - ($price + $credit);
Ok, I cleared the table and now I get the error: mysql_result() [function.mysql-result]: Unable to jump to row 5 on MySQL result index 3?

Ok, I got that fixed, but now $lastbalance is -1.00, the last value from $result, but it doesn't change after that.

Oh, nevermind. The number after $result refers to the row id, so it's getting the data from the 5th row from the bottom. So, I can select any row number I want, but how do I select the top (one most recently inserted)?

Posted: Sat May 19, 2007 3:24 pm
by califdon
Just a couple of points:

The SQL INSERT statement adds new rows at the end of the table, not to the top. But you should not depend on the "last" (in terms of time) to always be at any particular place in a table. If you need to refer to the "most recent", you need to have a timestamp field in each record, then you can query in descending order of the timestamp and include LIMIT 1 in your query (assuming you're using MySQL, other databases may not support the LIMIT clause, but they usually have an equivalent, such as TOP 5).

I wouldn't use the row parameter of the mysql_request() function, but others may not agree.

Posted: Sat May 19, 2007 3:42 pm
by patrick24
Ok califdon, I think I get what your saying. This is my code:

Code: Select all

$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");
    
mysql_select_db($db) or die ("Unable to select database!");
	
$query = "SELECT balance FROM $customer WHERE id=(SELECT MAX(id))";
	
$result= mysql_query($query) OR die( 'Error in query:<br />' .$query. '<br />' .mysql_error() ); 
	
$lastbalance = mysql_result($result, 0);
	
$balance = ($lastbalance - $price + $credit);
    
$query1 = "INSERT INTO $customer (date, drink, credit, price, balance) VALUES ('$date', '$drink', '$credit', '$price', '$balance')";
    
$query2 = "SELECT * FROM $customer ORDER BY id DESC";

$result1 = mysql_query($query2) or die ("Error in query: $query2. ".mysql_error());

if (mysql_num_rows($result1) > 0) {
    echo "<table cellpadding=10 border=1>";
    while($row = mysql_fetch_row($result1)) {
        echo "<tr>";
	echo "<td>".$row[0]."</td>";
        echo "<td>".$row[1]."</td>";
        echo "<td>".$row[2]."</td>";
	echo "<td>".$row[3]."</td>";
	echo "<td>".$row[4]."</td>";
	echo "<td>".$row[5]."</td>";
        echo "</tr>";
    }
    echo "</table>";
}
else {

    echo "No rows found!";
}

mysql_free_result($result);

mysql_close($connection);
}
I have field id set as my primary key and using auto_increment. So, my result is ORDER BY id DESC so the most recent insert (max id) is at the end (top) of the table. So shoudln't $query = "SELECT balance FROM $customer WHERE id=(SELECT MAX(id))"; work to select the most recent insert query?
I'll try inserting a timestamp field and selecting that for the most recent balance.
Thanks.

Posted: Sat May 19, 2007 5:08 pm
by patrick24
Ah yes! I used $query = "SELECT balance FROM $customer ORDER BY ts DESC LIMIT 1"; and that worked! Thanks califdon and bdlang for all your help! You guys rock! :D