Computer Science Canada

PHP / MySQL Noob need's some help with News!
Author:  Leftover [ Fri Jan 21, 2005 7:46 pm ]
Post subject:  PHP / MySQL Noob need's some help with News!
Well I've tried several tutorials online and none of them are working for me. The company I worked with (Web Development Firm) recently went under before I got to learn the jist of PHP and MySQL and such.

I am basically working on a page for a local band here, and I need a news page. I want to make a MySQL form to add the news to the DB, which I think I can do, so that the band members can add news them selves without modifying the page, and possibly / most likely messing something up in the process.

What I have now is a page using IFRAME, and it loads my news page. The problem I have is A: making the database and B: calling to the database (I can't truely test it but there were a few things I didn't understand)

I want to make a MySQL db for news to start with. I want it to show 4 things. A picture themed to the page with the day on it, under that a time stamp with time and date in short format, a title for the news article, and the body of the news. I then need to know the code to call to it from the php page for news, and make the form page for submitting.

I talked to my old boss and he didn't have time to help me, just told me to blow money on a PHP book. Any help would be great and put to good use for a local band.



F.Y.I. The errors I was having creating the table in phpMyAdmin was #1064, check for right syntax to use near '(100)DEFAULT 'pic' NOT NULL, 'date' TIMESTAMP(20) DEFAULT 'date'

I tried a bunch of tutorials on using phpMyAdmin and this one was the one I got the least errors on.
Author:  Blade [ Fri Jan 21, 2005 10:00 pm ]
Post subject: 
okay, i am not sure how much you know about this kind of stuff so to cover all bases iam gonna write it noob like.

a mysql server has databases, in the databases are tables. tables have set "fields" which you can think of as colomns.. each row will have different information.. ie:

table news:

blade:

id | topic  |  message
------------------------
1  | test   | testing the news
2  | test2  | second news test


just to make it easier the best way to do mysql stuff is to get a program called phpmyadmin, then edit the config file with your hostaddress (for your database), your username and password. phpmyadmin allows you fully control your database so it'd be best to put some type of security on it if you decide to use it. also the reasoning for using this is to make databases (as long as you have the proper permission on your mysql server), make tables etc.. what it sounds like is the only thing you want php to do is to read from the tables and insert new rows into the tables..

now. when you go into phpmyadmin you can create a new database if you need to, but some places give you your database so you are restricted to editing that one.. in this iam going to refer to your news table, as news. so you're going to want to create a table called news. you are going to need 5 fields in it from what you said you wanted it to display. the first one i always use in all my tables, it being the identifier, because you can have 2 of the same titles in your database or something similar. so we'll call that field id .. gonna be set as int and i usually set the size to 11 ... i also set id as the primary field and sometimes i set it to autoincrement... you may want to do this.. as for the other fields you will fill in the names according to what you want to be in them.. you said you wanted an image with the day on it.. so you may want to label that as iday .. and have a hyperlink created by php to determine which image to link it to.. you'll want that ot be varchar and maybe a length of 50.. 50 being the amount of characters (maximum being 255) the field is able to hold.. if it is more then mysql will return an error.. do the same with the rest of the fields... iam going to refer to your fields as: id, idate, date, title, body... now for the body you're going to want to set the type of that to text ... where there is no limit for the amount of characters it can hold .. (some other programs like phpmyadmin call it blob) .. so for that you will set no size...

now for the php part..
first off to connect
php:
<?
  $sqlink = mysql_connect("hostname", "username", "password") or die("Unable to connect to the database");
  mysql_select_db("databasename")or die("Unable to select the database");
?>


note: if you are unfamilar with any of these function, you can search for them at http://php.net and they will provide an explanation for the function

now that you are connected, you are able to retrieve information from tables

to retrieve all the information from a table you will have to loop it like this
php:
<?
  $sql = "select * from news order by id DESC";
  $sqlresult = mysql_query($sql);
  echo "<table>"
  while($record = mysql_fetch_array($sqlresult, MYSQL_ASSOC)){
    //for each time it goes through this loop it will retrieve 1 row then go onto the next, so this is where you'll output your content
    //iam going to output each element in a row... so you'll have to format it the way you want..
    echo "<tr><td><img src=\"" . $record['idate'] . "\">" . $record['date'] . "<br>" . $record['title'] . "<br>" . $record['body'];
  }
  echo "</table>";
  mysql_close($sqlink);
?>

ok now onto an explanation... first we establish a query with the server..
* means to select all fields, if you only want to select a few then the query would be
php:
$sql = "select idate, date from news order by id ASC";

order by id DESC means to select the rows and organize it by id in a descending fashion.. of course you can organize it by other fields .. or organize it the opposite way: ascending (ASC).. or you may choose not to organize it at all..which would look like
php:
$sql = "select * from news";

there are many ways you can select the rows from a table, but i choose to do it by putting the information into an array, and making the elements in the array named by the fields from the database.. if you want more information search the php function database and read up on it some more. oh, and just so you know the
php:
echo $record['title'] . "<br>" . $record['body'];

the periods are concatenation

if you only want to select one row from the database you can use this type of query
php:
$sql = "select * from news where id='1'";
$sqresult = mysql_query($sql);
$sqlinfo = mysql_fetch_array($sqresult, MYSQL_ASSOC);


ok now inserting to the table:
of course you are going to want to use a form to collect information.. when the form is passed to a php handler page, all the variables are by default stored into the $_POST array
HTML:
<!-- //insert.html -->
<form method="post" action="insert.php">
  Title:<br>
  <input type="text" name="title"><br>
  Body:<br>
  <textarea name="body"></textarea><br><br>
  <input type="submit" value="  Submit  ">
</form>

by the way you typed your message iam assuming that you know how to use a form
php:
<?
  //insert.php
  $title = $_POST['title']; //to make it easier to handle i have set them to more simple variables
  $body = $_POST['body'];

  if(empty($title)){ //check to see if the user has inputted any data
    die("You must enter a title to continue");
  }
  elseif(empty($data)){
    die("You must enter a body");
  }
  //cuz i'm a nice guy i'll give you examples on to how to make the calculations for the date
  //iam also not very clear on the style you want for your date, so research the date function at php.net
  //the most simple way to do your name your images mon.gif through to sun.gif or jpg whichever .. and get php to return the date in that format, the concatenate it onto the end of a reletive path.. ie below
  $idate = "images/days/" . strtolower(date("D")); //strtolower makes the string all in losercase
  $date = date('l, F d, Y'); //ie format: Sunday, January 16, 2005
  $sql = "insert into news values('', '$idate', '$date',  '$title', '$body')";
  $sqlresult = mysql_query($sql) or die(mysql_error());
  mysql_close($sqlink);
?>

when you do the mysql query make sure when you do the values theat they line up with the proper fields, otherwise you will get information inserted into improper fields..
the first one where it says '' are 2 single quotes, meaning you leave it empty.. only use this if you have autoincrement on .. otherwise you'll have to calulate the next number that will go in place in your id field..

if you have any questions you can concact me on msn at csthomas@sympatico.ca or email me there

edit: haha, this is the first time i've used the new syntax highliting mod, so it seems that all the functions are highlited with a link to php.net where it explains it
Author:  Leftover [ Tue Jan 25, 2005 12:21 am ]
Post subject: 
That was by far, the most indepth and informational and helpful reply I have ever recieved on these boards. I have added you to my MSN regarding a question or two. Thanks for all this Smile
Author:  Blade [ Tue Jan 25, 2005 2:10 am ]
Post subject: 
lol thanks... i was wondering why it hasnt said you've added me and thats because i got a new msn cuz the old one was messing up.. its now csthomas@sympatico.ca

:


You can syndicate this boards posts using the file backend.php or view the topic map using sitemap.php.

Terms of Use | Privacy Policy