mysql data is not showing up (messy scripting?)

Daria · #1 07-12-2005, 04:53 PM

here is my messed-up code:

PHP Code:


			
<?php require_once("inc-configuration.php"); ?>

<?php require_once("inc-database.php"); ?>

<?php

// Get all the press items from the "press_items" table for the press group that has the press_type_id 

// equal to the type id 2

$query["press_items"] = "SELECT press_id, press_group_id, press_type_id, press_order, press_title, press_source, press_date, press_page FROM press_items WHERE press_type_id = 2";

$resultID["press_items"] = mysql_query($query["press_items"], $linkID);

?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> 

<html>

<head>

<title>list</title>

</head>

<body>

  <table border="1" width="100%" cellspacing="2" cellpadding="2">

    <?php  

     while ( $row["press_items"] = @mysql_fetch_array($resultID["press_items"]) )  {    

        // START WHILE LOOP 

            $title = $row["press_title"];

            $source = $row["press_source"]; 

            $date = $row["press_date"];

            $link = $row["press_page"];

     echo "<tr bgcolor=#EEEEEE><td bgcolor=#EEEEEE>";

     echo "<a href=\"$link\"><b>$title</b></a><br>";

     echo $source;

     echo "<br>";

     echo $date;

     echo "</td></tr>"; 

    }  

    // STOP WHILE LOOP

    ?>     

  </table>

</body>

</html>

it outputs blank, i.e.:

PHP Code:


			
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 

Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> 

<html>

<head>

<title>list</title>

</head>

<body>

  <table border="1" width="100%" cellspacing="2" cellpadding="2">

    <tr bgcolor=#EEEEEE><td bgcolor=#EEEEEE><a href=""><b></b></a><br><br></td></tr>     

  </table>

</body>

</html>

obviously, at this time what I am trying to achieve is to list the titles (press_title) of the documents, contents of which (press_data) is stored in the mysql table (press_items), that have a type value of 2 (type_id = 2) - (for future use, there is another table, press_types, that is cross-referenced); each title is linked to a corresponging page press_page;
and for those items I want to list a couple of their fields: press_source and press_date (date is a varchar here...)

I cannot understand what am I doing wrong, how to fix... If someone could explain it to me, please...

NogDog · #2 07-12-2005, 07:08 PM

This line...

PHP Code:


			
while ( $row["press_items"] = @mysql_fetch_array($resultID["press_items"]) )  {

...is assigning the contents of each result row to the the array $row["press_items"]. To reference each column of that row, you'd then have to change the following lines to...

PHP Code:


			
            $title = $row["press_items"]["press_title"];

            $source = $row["press_items"]["press_source"];

            $date = $row["press_items"]["press_date"];

            $link = $row["press_items"]["press_page"];

Obviously, that's pretty ugly and, as far as I can tell from your code, unnecessary. Why not just get rid of all these unneeded array indices and use normal variables?

PHP Code:


			
$query = "SELECT press_id, press_group_id, press_type_id, press_order, press_title, press_source, press_date, press_page FROM press_items WHERE press_type_id = 2";

$resultID = mysql_query($query, $linkID);

/*.

  .

  .*/

     while ( $row = @mysql_fetch_array($resultID) )  {    

        // START WHILE LOOP

            $title = $row["press_title"];

            $source = $row["press_source"];

            $date = $row["press_date"];

            $link = $row["press_page"];

     echo "<tr bgcolor=#EEEEEE><td bgcolor=#EEEEEE>";

     echo "<a href=\"$link\"><b>$title</b></a><br>";

     echo $source;

     echo "<br>";

     echo $date;

     echo "</td></tr>";

    }

Daria · #3 07-13-2005, 09:12 AM

Thanks for such details!
on the sad note, I still get the blank page... This time I don't even get the <tr> portion of it that is supposedly generated.. i.e. the following line is missing

PHP Code:


			
<tr bgcolor=#EEEEEE><td bgcolor=#EEEEEE><a href=""><b></b></a><br><br></td></tr>

I have just one item (for starters) in the database now, and I made sure it has value 2 in the press_type field. I don't undersdand why it is not being called?

Daria · #4 07-13-2005, 02:24 PM

ok, SUPER-SIMPLIFIED version.
I have just one item, with id = 0

here is the page where I don't get to see anything. Please, explain to me what am I doing wrong?

PHP Code:


			
<?php require_once("inc-configuration.php"); ?>

<?php require_once("inc-database.php"); ?>

<?php

// Get all the press items from the "press_items" table for the press item

// that has the press_id equal to 0

$query["press_items"] = "SELECT * FROM press_items WHERE press_id=0";

$resultID["press_items"] = mysql_query($query["press_items"], $linkID);

?>

<html>

<body>

<!--  Show the press item title -->

<?php echo @mysql_result($resultID["press_items"], 0, "press_item_title"); ?>



</body>

</html>

Bootsman123 · #5 07-13-2005, 03:12 PM

With this, you can only get one result.

You should really use what NogDog said:

PHP Code:


			
while ( $row = @mysql_fetch_array($resultID) )  {

Daria · #6 07-13-2005, 03:17 PM

I tried NogDog's advice and was still getting a blank page!... I don't know why it didn't work for me.

anyway, I just solved it... I scratched the whole thing and re-did it step-by-step (plus including counting rows thing...) and got some results. Now all of my records (now 3, I added 2 more) are displaying. It's a start

so, here is what I ended up with:

PHP Code:


			
 <?php require_once("inc-configuration.php"); ?>

<?php require_once("inc-database.php"); ?>

<html>

<body>



<?

$query="SELECT * FROM press_items";

$result=mysql_query($query);

$num=mysql_numrows($result);



mysql_close();



echo "<table border=\"1\" width=\"100%\" cellspacing=\"2\" cellpadding=\"2\">";



$i=0;

while ($i < $num) {



            $title=mysql_result($result,$i,"press_title");

            $source=mysql_result($result,$i,"press_source"); 

            $date=mysql_result($result,$i,"press_date");

            $link=mysql_result($result,$i,"press_page");



         echo "<tr bgcolor=#EEEEEE><td bgcolor=#EEEEEE>";

         echo "<a href=\"$link\"><b>$title</b></a><br>";

         echo $source;

         echo "<br>";

         echo $date;

         echo "</td></tr>"; 



$i++;

}

echo "</table>";

?>





</body>

</html>

In the end, when I'm done with this one, I would like to have a relatively complicated task to be done,
I have 3 tables, one for articles (press_items), one for the years articles were published in (press_group), and one for the article type (press_type).

I would like to have visitors to view articles in 3 ways:

all by type, all by year, and narrowed results by both: certain year for certain type...

I'm still learning and would like to attempt to write the whole thing by myself - so this process would be a perfect hands-on experience. I would appreciate some pointers when I do something wrong, as well as a helping hand to explain why certain things I am doing are incorrect, and why the correct ones work...

Anyway.. THANKS FOR INPUT!

NogDog · #7 07-13-2005, 03:28 PM

Let's do some defensive coding (which is always a good idea) and find out where the problem is:

PHP Code:


			
$query = "SELECT press_id, press_group_id, press_type_id, press_order, " .

         "press_title, press_source, press_date, press_page " .

         "FROM press_items WHERE press_type_id = 2";

$resultID = mysql_query($query, $linkID);

if(!$resultID)  # invalid query

{

  echo "<p>ERROR: query failed: " . mysql_error() . "</p>\n";

  echo "<p>QUERY:<br>$query</p>\n";

}

elseif(mysql_num_rows($resultID) == 0)  # valid, but no matches returned

{

  echo "<p>ERROR: no rows were returned by the query.</p>\n";

}

else  # looks like query worked, so display results:

{

  while ($row = mysql_fetch_assoc($resultID) )

  {    

    $title = $row["press_title"];

    $source = $row["press_source"];

    $date = $row["press_date"];

    $link = $row["press_page"];

    echo "<tr bgcolor=#EEEEEE><td bgcolor=#EEEEEE>";

    echo "<a href=\"$link\"><b>$title</b></a><br>";

    echo $source;

    echo "<br>";

    echo $date;

    echo "</td></tr>";

  }

}

Daria · #8 07-13-2005, 03:31 PM

HOORAY! this one worked, NogDog

Daria · #9 07-13-2005, 03:33 PM

OK, here is the question of the day....

why in some examples we put
$query = "Select..." etc on the top, outside of <html> tag, and in some examples into <body> ?

NogDog · #10 07-13-2005, 03:42 PM

Quote:

Originally Posted by Daria

OK, here is the question of the day....

why in some examples we put
$query = "Select..." etc on the top, outside of <html> tag, and in some examples into <body> ?

In this particular case, it doesn't really matter, it's more a style and organization thing. The only times it would matter from a technical perspective would be if there were conditions where the result of some PHP command would cause you to want to do something that must be done before any output is sent to the browser, such as setting a cookie, redirecting via header(), etc. Those things would have to be in the script before any non-PHP text (including whitespace) appears on the page.

Daria · #11 07-13-2005, 04:01 PM

thanks

Daria · #12 07-14-2005, 11:18 AM

EDITED:::: BEFORE READING THIS, PLEASE, READ MY NEXT POST BELOW FOR A SHORT VERSION OF THIS GIBBERISH

=======================================================

OK, here is the follow-up question.

Now I am modifying the call to pull up only certain IDs

In this case I change

PHP Code:


			
"FROM press_items WHERE press_type_id = 2 ";

to

PHP Code:


			
         "FROM press_items WHERE press_type_id = '" . $_GET["gid"] . "' ";

in this case when I have my *.php?id=0 or *.php?id=2 I get the links to the files assigned to those categories, respectively. Works great.

Now, here is the tricky part:

press_page is an actual part of a url, like

file-article-type-one.php

so the links show up like something like this:

PHP Code:


			
<a href="press_room/file-article-type-one.php">Article - Type One</a>

the part that drives me nuts is this press_room/ folder - because that's where I store all the articles, and that's where they have to stay for various reasons. The link, obviously, works fine, and takes me to existing article.

Now, in that press_room folder, I have one file, press_template.php that goes like this (simplified version)

PHP Code:


			
<?php require('../inc-configuration.php'); ?>

<?php require('../inc-database.php'); ?>

<?php

$query2 = "SELECT press_data FROM press_items WHERE press_page = '".$_SERVER['PHP_SELF']."'";



$resultID = mysql_query($query2, $linkID);

$data = @mysql_result($resultID, 0, 'press_data');

?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"

"http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" press="text/html; charset=iso-8859-1">

<title>compensationresources.com</title>

</head>

...

<body>

<?php echo $data; ?>

...

</body>

</html>

Now, when I create a new page in the admin panel, and give it a page name
file-article-type-one.php (that's what is submitted into a DB as press_page)
and a title Article - Type One (that's what is submitted into a DB as press_title), it takes a press_template php (code above) and literally creates a page called file-article-type-one.php.

So I am relying on this line

PHP Code:


			
"SELECT press_data FROM press_items WHERE press_page = '".$_SERVER['PHP_SELF']."'"

to pull up the content from the database, based on the url.

Now
$data (actual content of an article) is not showing up. I suspect it is because of something I missed to accomodate for an extra folder in the path or something, or something equally simple....

Please, help me figure out this one, too... !!!!!!!!! I spent 2 days trying to move files around and see if having files in our out of press_room/ folder it makes any difference, and no luck so far. what's up?

Daria · #13 07-14-2005, 11:26 AM

I guess the real question and a short version of what I am asking is

how to eliminate /press_room/

from that PHP_SELF? and read only what's after the latter / ?

If it cannot be done, I guess, as an alternative, I can include /press_room/ to be a part of the press_page field, so it would go as /press_room/file-article-type-one.php instead of just simply file-article-type-one.php....

BeachSide · #14 07-14-2005, 01:40 PM

PHP Code:


			
$url = $_SERVER['PHP_SELF'];

$filename = substr($url, strrpos($url, '/') +1);

Will grab the filename

Daria · #15 07-14-2005, 01:54 PM

ihhh

now, how do I tie it in with

PHP Code:


			
<?php

$query = "SELECT press_data, press_page, press_title, press_source, press_date FROM press_items WHERE press_page = '".$_SERVER['PHP_SELF']."'";

$result = @mysql_query($query, $linkID);

?>

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)