homepage Welcome to WebmasterWorld Guest from 54.198.94.76
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
mysql
retrieve data
ricksum




msg:4587135
 1:57 pm on Jun 24, 2013 (gmt 0)

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




msg:4587186
 5:03 pm on Jun 24, 2013 (gmt 0)

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




msg:4587206
 5:43 pm on Jun 24, 2013 (gmt 0)

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




msg:4587210
 5:56 pm on Jun 24, 2013 (gmt 0)

ALERT:

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

ricksum




msg:4587216
 6:21 pm on Jun 24, 2013 (gmt 0)

Isn't that the same as Glenn R?

swa66




msg:4587330
 6:25 am on Jun 25, 2013 (gmt 0)

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




msg:4587412
 12:10 pm on Jun 25, 2013 (gmt 0)

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




msg:4587453
 2:35 pm on Jun 25, 2013 (gmt 0)

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




msg:4587518
 7:01 pm on Jun 25, 2013 (gmt 0)

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




msg:4587542
 8:10 pm on Jun 25, 2013 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved