Page 1 of 1
select statement from more than 1 column
Posted: Mon Nov 04, 2002 5:56 am
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
Posted: Mon Nov 04, 2002 6:24 am
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.
I am using mysql
Posted: Mon Nov 04, 2002 6:45 am
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
Posted: Mon Nov 04, 2002 6:50 am
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
Posted: Mon Nov 04, 2002 7:00 am
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?
from another included page
Posted: Mon Nov 04, 2002 7:08 am
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
Posted: Mon Nov 04, 2002 7:22 am
by jonsyd
bit tricky to work out what the problem is without error messages, if you're not getting any, put:
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
ok
Posted: Mon Nov 04, 2002 7:33 am
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;
?>
Posted: Tue Nov 05, 2002 4:19 am
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
they do work
Posted: Tue Nov 05, 2002 5:18 am
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

mysql_fetch_object
Posted: Tue Dec 03, 2002 3:33 pm
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
nevermind
Posted: Wed Dec 04, 2002 7:34 am
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