Trying to retrieve selective results from a table.

icsys · #1 13-Nov-2003, 13:36

Hi.
I want to be able to retrieve selected results from a MySQL table. I'm no PHP programmer but i have been playing around with the following code :

PHP Code:


<?php
   $link = mysql_connect("localhost", "*****", "*****") 
        or die("Could not connect : " . mysql_error()); 
    print "Compatible Models"; 
    mysql_select_db("*******") or die("Could not select database"); 

    /* Performing SQL query */ 
    $query = "SELECT phone_make,phone_model,content,status FROM gsm_compat WHERE ( content=8 ) AND ( status='yes' )"; 
    $result = mysql_query($query) or die("Query failed : " . mysql_error()); 

    /* Printing results in HTML */ 
    print "<table>\n"; 
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { 
        print "\t<tr>\n"; 
        foreach ($line as $col_value) { 
            print "\t\t<td>$col_value</td>\n"; 
        } 
        print "\t</tr>\n"; 
    } 
    print "</table>\n"; 

    /* Free resultset */ 
    mysql_free_result($result); 

    /* Closing connection */ 
    mysql_close($link); 
?>

If I leave out this bit:
WHERE ( content=8 ) AND ( status='yes' )";
I get ALL the results but when I add that bit back in I get nothing displayed.

Anyone know where I am going wrong?

Thanks in advance for any help.

JdS · #2 13-Nov-2003, 19:02

Hello icsys,

Maybe there's no record for content=8 or status='yes' or both; in the database table?

If it were just a syntax error, your code error checking should be reporting it...

icsys · #3 14-Nov-2003, 11:39

If only it were that simple

There are loads of matching records. It just wont extract them?

JdS · #4 14-Nov-2003, 17:18

OK, try this then:

PHP Code:


<?php
$query = "SELECT content, status FROM gsm_compat WHERE content=8";
?>

If it returns any record with

Code:

content | status
--------+--------
 8      | yes

then we know we have a SQL issue at hand....

icsys · #5 16-Nov-2003, 18:16

I tried the query as suggested and there were no errors reported but still no results?

JdS · #6 17-Nov-2003, 05:21

If you have access to mysql command line, try a few of these queries:

Code:

mysql> SELECT * from `gsm_compat` WHERE content=8;
mysql> SELECT * from `gsm_compat` WHERE status='yes';
mysql> SELECT `content`, `status` from `gsm_compat`;

icsys · #7 17-Nov-2003, 13:28

mysql> SELECT * from `gsm_compat` WHERE content=8;
returns no results
mysql> SELECT * from `gsm_compat` WHERE status='yes';
returns no results
mysql> SELECT `content`, `status` from `gsm_compat`;
returns content and status for all records

I'm begining to think there is a problem with this table.

JdS · #8 17-Nov-2003, 16:12

Quote:

Originally posted by icsys

mysql> SELECT `content`, `status` from `gsm_compat`;
returns content and status for all records

I'm begining to think there is a problem with this table.

Before you think that, are you CERTAIN at least one of these rows (see above) were either content=8 or status='yes'?

It might help if you post some rows that were returned by that last query... just some...not all!

Also you could copy and paste the results off the following query via the MySQL monitor:

Code:

mysql>  describe `gsm_compat`;

icsys · #9 17-Nov-2003, 17:00

Well after hours of frustration I think I have sussed it! And i cant believe it!

I am using a program called csv_importer to upload the csv extracts into the database. This is because our host server cannot accept load_data_local due to security issues (another story).
Anyway, this importer also appears to be importing the '' characters therefore it seems that this is why the sql query wont work.

I.e the records were 'yes' instead of yes (and like a dumbass I diddnt notice)

After much frustration and headache i noticed this and decided to delete a few rows and replace them without the '' and the bloody thing extracted!!!!

Thanks to all who tried to help with this.

The problem now is how do I stop the importer from importing these '' characters? I have no control over the csv format.
With over 900 records its going to be a ballache changing each record manually.

JdS · #10 18-Nov-2003, 03:31

I am glad to hear that you got this thing figured out...

My suggestion is to send the CSV to your host and get them to upload it to the table/db. If not, find a decent web host very quickly

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
Search Engine Positioning 101 and 201 "How To" Tips...	000	Search Engine Optimization Forum	0	29-May-2003 10:34
[Tutorial] MySQL Basics	nniehoff	MySQL / PHP Forum	15	23-Mar-2003 19:42