Query not working.. but it is fine :S

[rockinaway]

$forum = mysql_query('SELECT parent, last_post FROM test_boards WHERE id = '.$_GETS['file'].'');

 

That is my query but it doesn't seem to work. I have several other queries at the same time, they all work.... this one doesn't. This is in a WHILE statement with the others, but the variables were not returning any value.. What could be wrong?

[paul2463]

try

 

$forum = mysql_query('SELECT parent, last_post FROM test_boards WHERE id = '.$_GETS['file'].'')or die("error in query" . mysql_error());

 

see if it brings anything to light

[rockinaway]

No error at all...

[rcorlew]

YOu are using $_GETS instead of $_GET that could be the problem.

[rockinaway]

Still doesn't work..

[boo_lolly]

try:

$sql = "SELECT parent, last_post FROM test_boards WHERE id = '. $_GET['file'] ."'";
$forum = mysql_query($sql) OR die("[". $sql ."]<br />\nGave the following error: ". mysql_error());

 

and see what happens.

[paul2463]

try this

 

$file = $_GET['file'];
$forum = mysql_query("SELECT parent, last_post FROM test_boards WHERE id = '$file'")or die("error in query" . mysql_error());

 

if it returns no rows maybe there is no rows to return

[rockinaway]

Neither of those brought anything back.. not error

[paul2463]

then see if this will work

 

$file = 12; //or any valid id in your database, if it brings it back then $_GET['file'] doesnt carry what you want it to
$forum = mysql_query("SELECT parent, last_post FROM test_boards WHERE id = '$file'")or die("error in query" . mysql_error());

[rockinaway]

That didn't bring anything back either...

[boo_lolly]

what is the structure of your database?

[rockinaway]

Tne structure is fine as I have other queries from the same table and they work..

[boo_lolly]

Tne structure is fine as I have other queries from the same table and they work..

 

i didn't ask because i was concerned about your structure. i asked because it would help to know what your database looked like so i can help you write this code...

 

an example like:

+---------------------------------------------+
|                   table_name                |
+----------+-----------+------------+---------+
|  column1 |  column2  |   column3  | column4 |
+----------+-----------+------------+---------+
|  data    |    data   |     data   |  data   |
+----------+-----------+------------+---------+
|  data    |    data   |     data   |  data   |
+----------+-----------+------------+---------+
|  data    |    data   |     data   |  data   |
+----------+-----------+------------+---------+

 

would do fine.

[rockinaway]

+---------------------------------------------+

|                  test_boards              |

+----------+-----------+------------+---------+

|  id |  name  |  info | parent | last_post |

+----------+-----------+------------+---------+

|  1  |    Test1  |    text  |  0  | 34

+----------+-----------+------------+---------+

|  2  |    Test2  |    text  |  1  | 27

+----------+-----------+------------+---------+

|  2  |    Test3 |    text  |  1  | 15

+----------+-----------+------------+---------+

[boo_lolly]

i'm assuming 'id' isn't set to AUTO INCRIMENT (it needs to be), name is the name of the thread, info is the text of the thread, parent is probably the post that belongs to which thread, and last post i have no idea what that is... can you elaborate?

[rockinaway]

Sorry ...

 

id - is auto_incremented

name - name of board

info - desc

parent - parent board

last_post - last submission of file, this has the file ID

[boo_lolly]

so, every time a post is submitted to a particular thread, you are updating the 'last_post' column in your database with that id?

[rockinaway]

Yes, and when it is deleted I revert back to an older post..

[paul2463]

how are you interacting with the results resource of the query, maybe the problem lies in there

[paul2463]

Sorry ...

 

id - is auto_incremented

name - name of board

info - desc

parent - parent board

last_post - last submission of file, this has the file ID

 

$file = $_GET['file'];
$forum = mysql_query("SELECT parent, last_post FROM test_boards WHERE id = '$file'")or die("error in query" . mysql_error());

 

with the above in mind then which id are you calling for in the above query, the table id column which is auto-incremented or the file ID which is in the last_post column??

[rockinaway]

The id is of the incremental id..

[paul2463]

so as you answer in my last post about putting a valid auto-incremented id value in the query as opposed to using the $_GET['file'] variable, must pull something from the database, if you have a table entry with the ID value of 3 and use the following query

 

$file = "3";
$forum = mysql_query("SELECT parent, last_post FROM test_boards WHERE id = '$file'")or die("error in query" . mysql_error());

 

all things being equal and the spellings and values are correct, it WILL pull one row from the database, if it shows you nothing then it must be the way that you are interacting with the Result Resource $forum that is wrong somewhere

 

[HeyRay2]

Perhaps posting a little more of the related code is in order here. Namely, the code that you use to fetch and print your query results.

 

[boo_lolly]

Yes, and when it is deleted I revert back to an older post..

 

first of all that is NOT the way your database should be designed. there's no point in having a 'last_post' column and constantly adding an extra UPDATE query whenever a new post is added or deleted... that's not efficient. all you need is an auto-incrimenting 'id' column. take a look at this example table:

+-----------------------------------------+
|              test_boards                |
+------+--------+---------------+---------+
|  id  | parent |     title     |  data   |
+------+--------+---------------+---------+
|  1   |        |  title 1      |  text   |
+------+--------+---------------+---------+
|  2   |        |  title 2      |  text   |
+------+--------+---------------+---------+
|  3   |   1    |  post title1  |  text   |
+------+--------+---------------+---------+
|  4   |        |  title 3      |  text   |
+------+--------+---------------+---------+
|  5   |        |  title 4      |  text   |
+------+--------+---------------+---------+
|  6   |    2   |  post title 2 |  text   |
+------+--------+---------------+---------+
|  7   |    2   |  post title 3 |  text   |
+------+--------+---------------+---------+
|  8   |    1   |  post title 4 |  text   |
+------+--------+---------------+---------+

 

that's what your table should look like... and the way you can populate results is simple... just use a nested while loop.

 

<?php
        /*connect to your database here*/


        /*pull all threads*/
        $sql = "
                SELECT
                        *
                FROM
                        test_boards
                WHERE
                        parent = NULL
        ";
        $query = mysql_query($sql) OR die(mysql_error());

        while($thread = mysql_fetch_array($query)){
                echo $thread['title'] ."<br />\n";

                /*pull latest post from thread*/
                $sql = "
                        SELECT
                                MAX(id),
                                parent,
                                title,
                                data
                        FROM
                                test_boards
                        WHERE
                                parent = '". $thread['id'] ."'
                ";
                $query = mysql_query($sql) OR die(mysql_error());

                while($post = mysql_fetch_array($query)){
                        echo "<font size=\"-1\"><b>Last Post:</b> {$post['title']} | ". substr(0, 15, $post['data']) ."...</font><br />\n";
                }
        }
?>

 

that should do it.

[rockinaway]

The db structure is fine for the project I am doing, I am not allowed to give out any further details, sorry. But why won't that one query work?!