Displaying Fields from MySQL... 1

[Neil Toulouse]

Hi there!

I am a PHP newbie so please bear with me

I have a client table containing ID (auto_increment), SURNAME and FIRSTNAME fields.

I can connect to the database and pass a query no problems. What I am trying to achieve is after inserting a record in to the table, I want to requery the table returning the last inserted record and then do two things.

Firstly, assign the SURNAME and FIRSTNAME to 2 variables and then display them.

Secondly, I want a third variable (say $newval) that is a concatenation of the first 4 letters of the surname, the first letter of the surname and then the ID padded out to 10 characters. This will then become CLIENTNO in the table.

For example:

ID = 1
SURNAME = Bloggs
FIRSTNAME = Joe
CONCATENATION = BLOGJ0000000001 (this bit I am really stuck on!!)

So, after connecting to the database I am doing the following:

$query = "INSERT INTO client (Surname, FirstName) VALUES ( 'Bloggs', 'Joe' )";
$result = mysql_query($query) or die("Query failed : " . mysql_error());
$lastid = mysql_insert_id();

(works fine)

$query = "Select * from client where ClientID = $lastid" ;
$result = mysql_query($sSQL) OR DIE ("Query Failed"

(not sure if this is working, but no error displayed)

...what do I do next? How do I assign the SURNAME and FIRSTNAME to variables, then display them and then the concatenation?

Presumably afterwards I would then do:

$query = "UPDATE client SET ClientNo = $newVal WHERE ID = $lastID";
$result = mysql_query($query) or die("Query failed : " . mysql_error());

Any help much appreciated!

TIA
Neil

"I like work. It fascinates me. I can sit and look at it for hours..."

 

[sleipnir214]

To fetch the data after the SELECT query, use one of PHP's mysql_fetch_*() functions: mysql_fetch_row(), mysql_fetch_array(), mysql_fetch_object(), etc. There's some good example code here:

http://us4.php.net/manual/en/function.mysql-fetch-array.php

For fetching what I know to be one line of code, I usually perform:

list ($id, $first, $last) = mysql_fetch_row($result);


The concatenation is not hard, either. Take a look at substr() (

http://us4.php.net/manual/en/function.substr.php)

and sprintf() (

http://us4.php.net/manual/en/function.sprintf.php)

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[Neil Toulouse]

Many thanks for that, it works! This has been driving me mad for a couple of days as my variables always had the value of 0. The LIST function is exaclty what I needed!

Now for the padding...

Neil

"I like work. It fascinates me. I can sit and look at it for hours..."

 

[Neil Toulouse]

$sSQL = "Select clientid, surname, firstname from client where ClientID = $lastid" ;

$result = mysql_query($sSQL) OR DIE ("Query Failed"

list ($id, $last, $first) = mysql_fetch_row($result);

$newval =substr($last,0,4).substr($first, 0, 1).str_pad($lastid, 10, "0", STR_PAD_LEFT);

$newval = strtoupper($newval);

echo $newval; //Works fine, but...

$query = "UPDATE client SET ClientNo = '$newval' WHERE ClientID = $lastID";
$result = mysql_query($query) or die("Query failed : " . mysql_error()); ''Fails (incorrect syntax type of error)

Any help?

TIA
Neil


"I like work. It fascinates me. I can sit and look at it for hours..."

 

[Neil Toulouse]

sorted it.

"I like work. It fascinates me. I can sit and look at it for hours..."

 

[jstreich]

You may also want to add slashes before inserting and strip slahes before displaying....

//inserting
$last = addslashes($last);
$first = addslashes($first);
$query = "INSERT INTO client (Surname, FirstName) VALUES ( '$last', '$first' )";
$result = mysql_query($query) or die("Query failed : " . mysql_error());
$lastid = mysql_insert_id();

$query = "Select * from client where ClientID = $lastid" ;
$result = mysql_query($sSQL) OR DIE ("Query Failed");
list($clientid, $last, $first) = fetch_mysql($result);
print "Name:" . stripslashes($first) . " " . stripslashes($last);

When the user uses chars like the single quote and such into their name, it will turn into SQL, so the could potentially issue SQL commands to bad things like a name:
'; DELETE FROM client *

Even something harmless like a name:
John's Dog
would cause an error. so the addslashes adds escape chars arround the offending spaces:

\'\; DELETE FROM client \*


So when you get it back, you'll need to strip the slashes again.

Also, the assignment returns false when the array your asigning from is empty, so to list all the names you can:

$result = mysql_query($sql);
while(list($last, $first) = mysql_fetch_row($result))
{
  print "Name:" . stripslashes($first) . " " . stripslashes($last);
}