Verify if a specific data exists in a table

Richardknox · #1 01-Jun-2007, 11:18

Hello,

I know how to write some elementary php scripts to interact with a mysql database. But I am confusing myself right now on how to create this script.

I have a database that will include specific location information on each webcam that is on my site. (lat/lon, weather codes, UTC, etc.)

Here is the table info: (extra info deleted)

CREATE TABLE `weather` (
`country` char(25) NOT NULL,
`state` char(25) NOT NULL,
`city` char(25) NOT NULL,
) ENGINE=MyISAM ;

I want to check this table to see if the information exists already or not, and if it does not exist, then add the data.

I can have multiple webcams that have the same 'United States', 'Alaska', 'Juneau' , but this table will only have one entry per 'United States', 'Alaska', 'Juneau'.

I have read that you should be able to query the table and get a true/ false response as to whether this location exist already. But so far my attempts are falling short.

PHP Code:


<?

$data1 = "United States";
$data2 = "Alaska";
$data3 = "Juneau";

$country = $data1;
$state = $data2;
$city = $data3;
$weather = "Test Weather Info";
$radar = "Radar Test Info";
$gmt = "UTC";


$results3 = mysql_query("SELECT * FROM `weather` WHERE `country` LIKE '$data1' AND `state` LIKE '$data2' AND `city` LIKE '$data3' LIMIT 0 , 30");

if (!$results3) {

       $query3 = "SELECT * FROM weather_country WHERE country = ('$country')";
        $result3 = mysql_query($query3);
        $row3=mysql_fetch_row($result3);
        
        
       
           $sql2 = "INSERT INTO weather (country, country2, country3, state, city, code, radar_id, gmt) VALUES ('$country', '$row3[1]', '$row3[2]', '$state', '$city', '$weather', '$radar', '$gmt')";
        
        @mysql_query ($sql2, $dblink);


    echo 'Could not run query: ' . mysql_error($dblink);
    exit;
}


        ?>

Thanks,
Richard

http://www.thewebcamsite.net

admin · #2 01-Jun-2007, 11:35

I think you could get away with using:

Code:

REPLACE INTO `weather`
  ( `country`, `state`, `city` )
  VALUES
  ( 'United States', 'Alaska', 'Juneau' )

But for this to work, you need to set at least a primary key or a unique key i.e. a key comprising all three columns.

Also, you might want to redesign your MySQL table. For one, it's not normalised in the first form i.e. so many repeated data in columns like `country`, and `state`, not to mention the likelyhood that the data will be tainted with typos e.g. US, USA, United States, U. States, etc.

Richardknox · #3 01-Jun-2007, 11:53

How would you make a primary key comprising those three columns??

Also, you stated normilized ??

Quote:

Also, you might want to redesign your MySQL table. For one, it's not normalised in the first form i.e. so many repeated data in columns like `country`, and `state`, not to mention the likelyhood that the data will be tainted with typos e.g. US, USA, United States, U. States, etc.

What should I be doing??

Thanks,
Richard

admin · #4 01-Jun-2007, 12:16

Well, I would probably create at least 3 MySQL DB tables that look like this:

Code:

TABLE NAME: countries
=====================
ISO_3166  | country
----------+------------
AS        | American Samoa
AT        | Austria
AU        | Australia
US        | United States of America


TABLE NAME: world_states
========================
country_states_id | country_code | state
------------------+--------------+-----------
1                 | US           | Alaska
2                 | US           | Alabama
3                 | US           | Arizona


TABLE NAME: weather
===================
weather_id | country_states_id | city      | weather_data
-----------+-------------------+-----------+-----------
1          | 1                 | Juneau    | rain
2          | 1                 | Haines    | snow
3          | 1                 | Deering   | cloudy

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Asynchronous transfer question	crystalattice	Miscellaneous Programming Forum	2	24-Jan-2007 21:39
[Include] Doubly-linked List	dsmith	C Programming Language	6	14-Apr-2006 14:12
Strange C++ code memory leakage problem	gaoanyu	C++ Forum	7	04-Nov-2005 09:09
Help extracting specific data from text file	wariner	C++ Forum	4	24-Apr-2005 04:14
[CONTEST?]Data Structure Test	dsmith	C Programming Language	2	06-Jun-2004 16:13

Verify if a specific data exists in a table

Re: Verify if a specific data exists in a table

Re: Verify if a specific data exists in a table

Re: Verify if a specific data exists in a table