[SOLVED] need help to understand and optimize php/mysql code

[gb]

Hi,

 

I'm a web designer dabbling in a bit of php code. I'm not much of a coder I admit 

 

I'm trying to put together a little News Item CMS that i can use with clients sites so they can update a news page themselves. I've managed to create the Admin upload to database stuff using Code Charge Studio, but I can't use Code Charge Studio to create code to use in existing clients web pages as an include. CCS is a templating system and doesn't like being included into non-CCS pages (all that stuff is beyond me).

 

So I have cobbled together some PHP from my limited understanding to create a little include that will work with clients sites.

 

This might sound strange, but i don't really no why a certain part of it works. The script actually works on my local server anyway (installed via XAMPP).

 

I cobbled together a 'for' loop because I didn't know how to use $NumberOfItems (just a number set in mysql to limit the number of news items printed out) in a 'while' loop, I didn't think it would work, but it does, I couldn't see how it iterates the $row arrays but it does :

 

^{//Print out News Items.}

 

^{for ($i = 0; $i < $NumberOfItems; $i++) {}

 

^{$row = mysql_fetch_array($result_NewsItems, MYSQL_BOTH);}

 

^{echo '<p>' . $row['news_Item_Date'] . '</p>';}

^{echo '<p><strong>' . $row['news_Item_Heading'] . '</strong></p>';}

^{echo '<p>' . $row['news_Item'] . '</p>';}

^{echo '<p>' . ' ' . '</p>';}

 

^}

 

Can somebody explain that, is there a better way of doing that or is that exceptable coding practise?

Is the rest acceptable coding or is it really a dogs breakfast even though it seems to work.

 

This is the entire script and below it is another script that crashes where i am trying to get the date output in a better format :

 

^<?php

 

^{//News_Include_Page_2.php}

 

^{$host = "localhost";}

^{$user = "root";}

^{$password = "password";}

^{$dbName = "db1";}

 

 

^{//Make the connection.}

 

^{$dbc = mysql_connect ($host, $user, $password) OR die ('Could not connect to MySql: ' . mysql_error());}

 

 

^{//Select the database. '@' is error suppression operator.}

 

^{@mysql_select_db ($dbName) OR die ('Could not select the database: ' . mysql_error());}

 

 

^{//Make the queries.}

 

^{$query_NewsItems = "SELECT news_Item_id, news_Item_Date, news_Item_Heading, news_Item FROM news_items ORDER BY news_Item_id DESC";}

^{$query_NumberOfItems = "SELECT No_of_NewsItems FROM no_of_newsitems_to_display";}

 

 

^{//Run the queries.}

 

^{$result_NewsItems = @mysql_query ($query_NewsItems);}

^{$result_NumberOfItems = @mysql_query ($query_NumberOfItems);}

 

 

^{//Get number of items to display from database.}

 

^{$row_NumberOfItems = mysql_fetch_array($result_NumberOfItems);}

^{$NumberOfItems = $row_NumberOfItems[No_of_NewsItems];}

 

 

^{//Print out News Items.}

 

^{for ($i = 0; $i < $NumberOfItems; $i++) {}

 

^{$row = mysql_fetch_array($result_NewsItems, MYSQL_BOTH);}

 

^{echo '<p>' . $row['news_Item_Date'] . '</p>';}

^{echo '<p><strong>' . $row['news_Item_Heading'] . '</strong></p>';}

^{echo '<p>' . $row['news_Item'] . '</p>';}

^{echo '<p>' . ' ' . '</p>';}

 

^}

 

^?>

 

script that crashes :

 

^<?php

 

^{//News_Include_Page_2.php}

 

^{$host = "localhost";}

^{$user = "root";}

^{$password = "password";}

^{$dbName = "db1";}

 

 

^{//Make the connection.}

 

^{$dbc = mysql_connect ($host, $user, $password) OR die ('Could not connect to MySql: ' . mysql_error());}

 

 

^{//Select the database. '@' is error suppression operator.}

 

^{@mysql_select_db ($dbName) OR die ('Could not select the database: ' . mysql_error());}

 

 

^{//Make the queries.}

 

^{$query_NewsItems = "SELECT news_Item_id, DATE_FORMAT (news_Item_Date, '%a %b %e %Y') AS my_date, news_Item_Heading, news_Item FROM news_items ORDER BY news_Item_id DESC";}

^{$query_NumberOfItems = "SELECT No_of_NewsItems FROM no_of_newsitems_to_display";}

 

 

^{//Run the queries.}

 

^{$result_NewsItems = @mysql_query ($query_NewsItems);}

^{$result_NumberOfItems = @mysql_query ($query_NumberOfItems);}

 

 

^{//Get number of items to display from database.}

 

^{$row_NumberOfItems = mysql_fetch_array($result_NumberOfItems);}

^{$NumberOfItems = $row_NumberOfItems[No_of_NewsItems];}

 

 

^{//Print out News Items.}

 

^{for ($i = 0; $i < $NumberOfItems; $i++) {}

 

^{$row = mysql_fetch_array($result_NewsItems, MYSQL_BOTH);}

 

 

^{echo '<p>' . $row['my_date'] . '</p>';}

^{echo '<p><strong>' . $row['news_Item_Heading'] . '</strong></p>';}

^{echo '<p>' . $row['news_Item'] . '</p>';}

^{echo '<p>' . ' ' . '</p>';}

 

^}

 

^?>

 

Thanks in advance for any help somebody might give.

 

Cheers

greg

 

 

[btherl]

That loop is fine.  It works because mysql_fetch_array() always returns the next row.  So each time around the loop, you are getting the next row.  I would write it like this instead:

 

while ($row = mysql_fetch_array($result_NewsItems, MYSQL_BOTH)) {
  echo '<p>' . $row['news_Item_Date'] . '</p>';
  echo '<p><strong>' . $row['news_Item_Heading'] . '</strong></p>';
  echo '<p>' . $row['news_Item'] . '</p>';
  echo '<p>' . ' ' . '</p>';
}

 

When there are no more rows left, mysql_fetch_array() will return false, and the loop will finish.  The advantage of this version is that you don't need to know how many items there are.  Instead, you let mysql_fetch_array() tell you when to stop.

[teng84]

i dont want to read that kind of long codes

 

but try doing like this instead of querying two time to get the number of rows to determine the loop count

 

$query_NewsItems = "SELECT news_Item_id, news_Item_Date, news_Item_Heading, news_Item FROM news_items ORDER BY news_Item_id DESC";
$result = mysql_query($query_NewsItems);
while($value=mysql_fetch_assoc($result))
{
//the the echo here
echo $value['news_Item_id'];

}

[gb]

OK guys, thanks, so basically it's acceptable enough, just not very elegantly coded.

 

Any idea why it crashes when i change these parts to get a better date formatting :

 

^{$query_NewsItems = "SELECT news_Item_id, DATE_FORMAT (news_Item_Date, '%a %b %e %Y') AS my_date, news_Item_Heading, news_Item FROM news_items ORDER BY news_Item_id DESC";}

 

and

 

^{echo '<p>' . $row['my_date'] . '</p>';}

 

Cheers

greg

[btherl]

Instead of

 

$result_NumberOfItems = @mysql_query ($query_NumberOfItems);

 

do

 

$result_NumberOfItems = mysql_query ($query_NumberOfItems) or die("Error in $query_NumberOfItems: " . mysql_error());

 

That will tell you if there are any errors in your SQL.

[gb]

Instead of

 

$result_NumberOfItems = @mysql_query ($query_NumberOfItems);

 

do

 

$result_NumberOfItems = mysql_query ($query_NumberOfItems) or die("Error in $query_NumberOfItems: " . mysql_error());

 

That will tell you if there are any errors in your SQL.

 

this is the errorI get btherl :

 

^{Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Program Files\xampp\htdocs\News_Items_CMS\News_Include_Page_2.php on line 45}

 

Line 45 is :

 

^{$row = mysql_fetch_array($result_NewsItems, MYSQL_BOTH);}

 

greg

[btherl]

Did you check for errors in all of your mysql queries?  Especially in the one that returns $result_NewsItems?  Make a change to mysql_query() similar to the one in my previous post (adding die(...))

[gb]

Did you check for errors in all of your mysql queries?  Especially in the one that returns $result_NewsItems?  Make a change to mysql_query() similar to the one in my previous post (adding die(...))

 

Unbelievable  ,

 

I had this:

 

DATE_FORMAT (news_Item_Date, '%a %b %e %Y')

instead of this:

DATE_FORMAT(news_Item_Date, '%a %b %e %Y')

and that's all it was. It now works.

 

Thanks btherl, you're a champion 

 

It threw this error when I put in the error checking you suggested :

 

^{Error in SELECT No_of_NewsItems FROM no_of_newsitems_to_display: FUNCTION db1.DATE_FORMAT does not exist}

 

Thanks for everybodies help.

 

greg