[Resolved] registered words?

[akillez]

hey,
I'm having a bit of difficulties with the site i'm currently working on...

I'm using 19 different tables in a mysql dbase
who all get read by the same script.
But 3 of them won't show their data.

The tables in question are called 1sh, 2sd and rsd.
They have exactly the same structure as the others
and the same types of data in it.

I don't get an error message when running the script either...

Does anybody have a clue to hit me with?

$fields = "$page.ID as code,DATE_FORMAT($page.Date,'%d/%m/%Y') as dat,team1.name as t1,$page.HScore as s1,$page.AScore as s2,team2.name as t2";
$tables = "$page,teams as team1,teams as team2";
$where  = "$page.Home=team1.id and $page.Away=team2.id";
$order  = "$page.Date,$page.ID";

$query  = "select $fields from $tables where $where order by $order";
$result = mysql_query($query);

$page would be 1SH, 2SD or RSD.

[bastien]

try this:

$result = mysql_query($query)or die("can't complete query because ".mysql_error());

[akillez]

tnx for the reply
but i don't get an error msg, just a blank screen...

[bastien]

that means the query should be okay, but it either returns no rows or there is a problem in the code further down....

try echoing out the $query and cut'n'paste into your fav mysql client (phpmyadmin or something like that) and seeing what the result there is...if you get rows returned, then its the code below hadnling the results...if no rows, then the query needs tuning

[akillez]

phpMyAdmin says: "Your SQL-query has been executed successfully" :glare:
[mutter]you lying piece of ************ electronic crap[/mutter]

anyway, enuff for today
maybe tomorow it will all work like a charm... wouldn't that be nice 🙂

tnx again for your time and g'night

[FMB]

First off, reserved words can be found here:

PHP: http://us2.php.net/manual/en/reserved.php
MySQL: http://dev.mysql.com/doc/mysql/en/Reserved_words.html

Secondly, keep your variables out of the quotes:

$fields = $page. ".ID as code, DATE_FORMAT(" . $page . ".Date,'%d/%m/%Y') as dat,team1.name as t1, " . $page . ".HScore as s1," . $page . ".AScore as s2,team2.name as t2";
$tables = $page . ",teams as team1,teams as team2";
$where  = $page . ".Home=team1.id and " . $page . ".Away=team2.id";
$order  = $page . ".Date, " . $page . ".ID";

$query  = "select " . $fields . " from " . $tables . " where " . $where . " order by " . $order;
$result = mysql_query($query);

Then check $query and also check $page to see if they are correct.

[akillez]

checked that
checked that
did that
query works on 15 other tables
checked that
...no luck

tnx for the input fritz 😉

[FMB]

Problem might be in the order, try to order on dat instead of $page.Date.

Altho it works on 15 other tables :s

[akillez]

OK, when i split the query in 2 (one for each table) it runs without problems, so it must be the way it's linked right?
I'm not very good with joins though... so anybody willing to make my day and come up with a working query?

I would be eternally gratefull 😃

// structure + data example:
// teams = id("1SH_01");name("R. Antwerp Eagles");
// 1sh = ID("SH01");Date("2004-04-16");Home("1SH_07");Away("1SH_06");HScore("3");AScore("8")

$q_names = "select id,name from teams where id like '1SH%'";
$r_names = mysql_query($q_names);

$q_stats  = "select ID,DATE_FORMAT(Date,'%d/%m/%Y'),Home,Away,HScore,AScore from 1SH order by Date,ID";
$r_stats = mysql_query($q_stats);

[FMB]

ok, after this, I don't have any clues to hit you with, only clubs 😉

$tables = "$page,teams as team1,teams as team2";

make that

$tables = $page . " as team0 ,teams as team1,teams as team2";

so the total would be:

$fields = "team0.ID as code, DATE_FORMAT(team0.Date,'%d/%m/%Y') as dat,team1.name as t1, team0.HScore as s1,team0.AScore as s2,team2.name as t2";
$tables = $page . " as team0 ,teams as team1,teams as team2";
$where  = "team0.Home=team1.id and team0.Away=team2.id";
$order  = "team0.Date, team0.ID";

$query  = "select " . $fields . " from " . $tables . " where " . $where . " order by " . $order;
$result = mysql_query($query);

[FMB]

Originally posted by akillez
OK, when i split the query in 2 (one for each table) it runs without problems, so it must be the way it's linked right?
I'm not very good with joins though... so anybody willing to make my day and come up with a working query?

I would be eternally gratefull 😃

// structure + data example:
// teams = id("1SH_01");name("R. Antwerp Eagles");
// 1sh = ID("SH01");Date("2004-04-16");Home("1SH_07");Away("1SH_06");HScore("3");AScore("8")

$q_names = "select id,name from teams where id like '1SH%'";
$r_names = mysql_query($q_names);

$q_stats  = "select ID,DATE_FORMAT(Date,'%d/%m/%Y'),Home,Away,HScore,AScore from 1SH order by Date,ID";
$r_stats = mysql_query($q_stats);

[/B]

HUH? where id like '1SH%' ???
That (could) result in a bigger result set than "where is = "1SH_01"' therefor, I question myself, don't you have a typo in one of the tables in the ID field or HOME field?

[akillez]

I changed the query, it still doesn't work but atleast it looks a bit cleaner and more readable 🙂

I also checked the tables, no typo's...
Returning a bigger result set is quite impossible since it's the prefix wich changes everytime:

1SH_01
1SH_02
...
2SH-VR_01
2SH-VR_02
...
2SH-ZA..
RSH..
...

[akillez]

:o ho-hum :o

After 2.5 days of ripping hairs out of my skull i found the prob bob...
It seems that somehow my db got hacked and the evil cracker added a space in front of the "Home"-data of the 3 problem tables!!!
Making the data look like " 1SH_01" wich ofcourse can't be found by "SELECT ... LIKE '1SH%' ..." :rolleyes:

my humble apologies to those who bothered to read/write/think

[FMB]

Originally posted by FMB
I question myself, don't you have a typo in one of the tables in the ID field or HOME field?

😃 🆒 😉