mysql

retrieve data

[ricksum]

Here's the deal:
I have a database that I need to extract data from a specific field and save and display it.

Here's the code I currently use:
$query = "SELECT LOCATE(' ',owner_name) AS spaceno, char_length(owner_name) AS no, INSTR(owner_name, '&')as ampersand, owner_name, physical_address
FROM assessor";
$result = mysql_query($query) or die(mysql_error());
echo "This is used to assist in determining the owner's LAST name, FIRST name and SPOUSE name.<br />
by using the spaceno to locate succeeding spaces.<br />";
// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){

echo $row['owner_name']. " - first space at ".$row[spaceno]. " - length of owner_name = ". $row['no']. " - Ampersand at ". $row['ampersand']." address is ". $row['physical_address'];
echo "<br />";

The owner_name contains the last name first name and mates name which I have figured out how to obtain. What I want to accomplish is to store these three values for later use.

How to do this

[phranque]

welcome to WebmasterWorld, ricksum!

What I want to accomplish is to store these three values for later use.

where and how much later do you want to use these values?

[ricksum]

Actually I would like to use them immediately, just didn't know how to ask it.
Here's the code I'm now using:
SELECT
IF( LOCATE( ' ', `username` ) >0, SUBSTRING( `username` , 1, LOCATE( ' ', `username` ) -1 ) , `username` )
AS memberfirst,
IF( LOCATE( ' ', `username` ) >0,
SUBSTRING( `username` , LOCATE( ' ', `username` ) +1 ) , NULL )
AS memberlast
FROM `users`
which allow me to get the first and last names, but I would like to add is a locate an & that would allow me to get the mate's name:
For Example:
If username = 'Sumrall Glenn R & Julia' the desired result would be memberlast='Sumrall' and memberfirst = 'Glenn R' and another field membermate='Julia'

[DrDoc]

ALERT:

What if username is "Smith Johnson Jack & Jill"?
What if username is "Jones Jean Paul & Sibylla"?

[ricksum]

Isn't that the same as Glenn R?

[swa66]

Your data seems to me to not be normalized and that's going to continue to cause trouble forever.

Easiest way around it is to fix the datamodel first. and then clean up the mess.

[ricksum]

Sorry if I seem to be stupid, which I may be, but I've just begun using php/mysql and have no idea what I doing except trying to learn.

What do you mean by 'normalized' and how do I accomplish that?

I've read up on normalization and the most I could determine is to eliminate duplicate columns ... what are the duplicates and then what needs to be done to clean up the mysql mess?

Can you suggest a good tutorial for learning php/mysql.

[swa66]

Trick is not to store things like "Smith Johnson Jack & Jill"
Store First Name, Last Name, Spouse Name, etc all in separate columns in your database.

Actually: use the relationship capabilities of mysql and store the owner(s) in a separate table from the property and reference them via an intermediate table (as one property can have multiple owners and one owner can own more than one property).

And if you're still learning: stop using the mysql interface, it's obsolete.
Use the mysqli (note the i) interface instead.

[ricksum]

Thanks for the help/info. I'll redesign my db.

One last question are the mysql and mysqli commands the same. If not, how do I implement mysqli?

Thanks again.

[swa66]

mysqli: [php.net...]

I'd suggest to focus a bit on the ability to use prepared statements (they offer quite a bit extra protection against SQL injection if used properly.