select statement from more than 1 column

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ridwan
Forum Commoner
Posts: 55
Joined: Thu Aug 22, 2002 3:15 am

select statement from more than 1 column

Post by ridwan »

I have posted this before with no solution; so maybe this time i will be a bit luckier.

I have a select statement which needs to select info from two columns in one row.

For Example: SELECT * FROM table1 WHERE column1 = 'info1' AND column2 = 'info2'

This should work but it doesn't seem to as nothing appears when I run the script, and there is definite info in those fields.

Is there something I'm missing in my statement and is it typed correctly

thanks
User avatar
jonsyd
Forum Commoner
Posts: 36
Joined: Fri Sep 20, 2002 9:28 am
Location: Oxford, UK

Post by jonsyd »

The SQL looks ok to me. Assuming you're using MySQL, what happens when you try the query using mysql from the command line (rather than through PHP)? This is a quick way to tell if your problem is in PHP or SQL.
ridwan
Forum Commoner
Posts: 55
Joined: Thu Aug 22, 2002 3:15 am

I am using mysql

Post by ridwan »

and it works when i run it in the sql command line I just don't uderstand why nothing shows up when I run it in an HTML page ??

This is the code I'm using

case "special_offer";

print "hello";

//recall info from the database
$query = "SELECT * FROM mecercontent WHERE subsection = 'intro' AND section = 'special offer' ";
$result = mysql_query($result) or die ("Error in query: $query. " mysql_error());

//get result set as object
$row = mysql_fetch_object($result);

$info = nl2br($row->information);

standardtable($info, "special offer", "#c0a269", "special offer");

break;
}


thanks
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Where are you getting your the information for the case statement in the switch? Are you sure that this is getting passed correctly - it is printing 'hello'? Have you tried using mysql_num_rows() to check to see how many rows are being returned?

Mac
User avatar
jonsyd
Forum Commoner
Posts: 36
Joined: Fri Sep 20, 2002 9:28 am
Location: Oxford, UK

Post by jonsyd »

Code: Select all

$result = mysql_query($result) or die ("Error in query: $query. " mysql_error());
should be:

Code: Select all

$result = mysql_query($query) or die ("Error in query: $query. " .  mysql_error() );
Oh yeah, any error messages?
ridwan
Forum Commoner
Posts: 55
Joined: Thu Aug 22, 2002 3:15 am

from another included page

Post by ridwan »

I sorted that out but now am just getting my standardtable to work but no info is being pulled from the database. If for example I take away the second row ->

Code: Select all

$query = "SELECT * FROM mecercontent WHERE subsection = 'intro' "
I get info filling into my table but as soon as I add the second column all the info disappears ???

This is weird as it works in mysql, the php code seems to be allright; is there nothing I might have overlooked.

Here is the code which fully worx, oh and by the way the 'standardtable' is a funtion which draws a table with a couple of arguments that I have to specify but otherwise it worx fine.

case "intro";

//recall info from the database
$query = "SELECT * FROM mecercontent WHERE subsection = 'special offers' AND section = 'intro'";
$result = mysql_query($query) or die ("Error in query: $query. " .mysql_error());

//get result set as object
$row = mysql_fetch_object($result);

$info = nl2br($row->information);

standardtable($info, "special offer", "#c0a269", "special offer");

break;

thanks
User avatar
jonsyd
Forum Commoner
Posts: 36
Joined: Fri Sep 20, 2002 9:28 am
Location: Oxford, UK

Post by jonsyd »

bit tricky to work out what the problem is without error messages, if you're not getting any, put:

Code: Select all

error_reporting (E_ALL);
at the top of the script.
Oh yeah, any chance you could use the

Code: Select all

tags in the posting form for blocks of code - its 10x easier to read the code
ridwan
Forum Commoner
Posts: 55
Joined: Thu Aug 22, 2002 3:15 am

ok

Post by ridwan »

I used the error reporting function but no error was given out, by the way I have error reporting turned on in my php.ini file.

Here is the code once again in proper form.....

Code: Select all

<?php
case "intro";

        //recall info from the database
        $query = "SELECT * FROM mecercontent WHERE subsection = 'intro' AND section = 'about mecer'";
        $result = mysql_query($query) or die ("Error in query: $query. " .mysql_error());

        //get result set as object
        $row = mysql_fetch_object($result);

        $info = nl2br($row->information);

        standardtable($info, "special offer", "#c0a269", "special offer");

        break;
?>
User avatar
jonsyd
Forum Commoner
Posts: 36
Joined: Fri Sep 20, 2002 9:28 am
Location: Oxford, UK

Post by jonsyd »

If you're not getting errors from PHP, it more than likely means there isn't a problem with your code but with the program logic, probably the switch statement. Does it switch properly?
As Twiglet suggested above, check that the different cases work, eg:

Code: Select all

case 'test1';
echo 'test1';
...
case 'test2';
echo 'test2';
... etc
ridwan
Forum Commoner
Posts: 55
Joined: Thu Aug 22, 2002 3:15 am

they do work

Post by ridwan »

i've tested them and they do work, maybe I'll just do like you said and relook at my code thanks anyway for helping :wink:
User avatar
khedron
Forum Newbie
Posts: 2
Joined: Tue Dec 03, 2002 3:33 pm
Location: RTP, NC, USA

mysql_fetch_object

Post by khedron »

mysql_fetch_object seems to be common with this issue and the code I'm using (this is someone's tutorial script I tweaked for testing purposes; I'm new at this as well...):

<?php
$db_host = 'localhost';
$db_user = 'username';
$db_pass = 'password';
$db_name = 'traceschar';
$db_table = 'Character';
$conn = mysql_connect($db_host,$db_user,$db_pass);
if ($conn == true) {
mysql_select_db($db_name,$conn);
$result = mysql_query("SELECT CharName from $db_table",$conn);
while($row = mysql_fetch_object ($result)) {
$tmp .= "Character : $row->CharName<br><hr>\n";
}
} else {
echo 'could not connect to database : '. mysql_error();
}
print $tmp;
?>


All of the table- and column names are case-sensitive and correct as you see them there (Character, CharName). I can change the $db_table variable to another tablename and change the fields as appropriate to that table, and they all work fine, even if I add columns to the query and add subsequent $tmp entries to match.

I'm thinking it's the table...

I have tried to rename the table to no avail, changing the script accordingly; I have exported to a CSV file and imported back into a same-named and a differently-named table, to no avail. The error it gives is similar to some I have seen in this forum, but none exactly...and WHY does it not work only on one table?

I've ensured the NULL/NOT NULL conditions are all fulfilled, checked syntax, and have multiple instances of the exact same script working on another table, with very slight changes. In fact, (of course one of the firsth things I tried) copying one of the working ones to a new name and changing the values in it to match the Character table also fails with:

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /www/larp/tracesofred/test/php/charsheet/chartest.php on line 48

Seeing as the original comments are still in it, line 48 won't translate for you here, but it's this one:

while($row = mysql_fetch_object ($result)) {

I noticed you were speaking of case above; I have noticed that phpMyAdmin displays the SQL code for my actions with all lowercase tablenames. I tried to reflect this in the code above, making them into 'character' and 'charname' to no avail also.

Please Help!

-Khedron de Leon
Gaming Geek
User avatar
khedron
Forum Newbie
Posts: 2
Joined: Tue Dec 03, 2002 3:33 pm
Location: RTP, NC, USA

nevermind

Post by khedron »

Unless I miss my mark, I have let my gaming terminology make me forget the basics of database admin. Character and Char...Duh.

I haven't tested it yet, but imagine this to be the issue...

-Khedron
Post Reply