Re: Table and PHP scripts for existing data

From: Geoff Berrow (blthecat_at_ckdog.co.uk)
Date: 03/11/04 

Date: Thu, 11 Mar 2004 17:31:34 +0000

I noticed that Message-ID:
<cbc70ed4.0403110727.2a0625e7@posting.google.com> from Aaron contained
the following:

>
>1) what should my table SQL statement look like for data (sample) as
>follows;
>
> "John Doe","Managing Partner","BM SECURITIES LTD.","P.O. Box
>201","Grand Cayman","Cayman Islands,"","","345--555-5999",

You already said you used phpMyAdmin. Set the table up using that. I'd
include a primary key that is an integer and set it to auto_increment.
Everything else is a VARCHAR (yes, even the phone number) I'll leave it
to you to decide what length. What about the two fields with no data?
What are they for?

>
>2) once the table is created inside my database on MySQL, how do I
>load the data in?

Assuming the data is a .csv or a .txt file you upload it and then us e a
scrtipt similar to the one below

In this case the file is called query1.txt

# Connect to the database
mysql_connect('host',user,pass);

# Delete the current content of the table
$result = mysql_db_query($sql_db,"DELETE FROM $table") or die ("Invalid
DELETE query");

# Optimize the current table (recover empty space)
$result = mysql_db_query($sql_db,"OPTIMIZE TABLE $table") or die
("Invalid OPTIMIZE query");

# Load local comma separated, fields enclosed by quotes text database -
File has to be in the same directory of this file
$result = mysql_db_query($sql_db,"LOAD DATA LOCAL INFILE 'query1.txt'
INTO TABLE $table FIELDS TERMINATED BY ';' ENCLOSED BY ''") or die
("Invalid DATA LOAD query");

# Get how many records are present in the table now
$result = mysql_db_query($sql_db,"SELECT * from $table") or die
("Invalid SELECT query");
$rows_count = mysql_num_rows($result);

echo "Records: $rows_count"; mysql_free_result($result);

?>
>
>3) what is the best choice of variables, operators and arrays for my
>PHP script, to display only records from a certain city, country,
>company name or Dealer name?

I think you need to work your way through a PHP/MySQL tutorial

Try http://hotwired.lycos.com/webmonkey/99/21/index2a.html 

-- 
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

Relevant Pages