Computer Science Canada

Read/Write to mysql database, and a little phpmyadmin
Author:  Blade [ Wed Jan 26, 2005 4:02 pm ]
Post subject:  Read/Write to mysql database, and a little phpmyadmin
I originally posted this in php help as a response to a question, but since it was so detailed and tutorial-like i decided to edit a bit to generalize it and post it in here. the scenerio was he needed to create a database, and wanted to display news on the site. he wanted it to display the date, a picture with the date, the title, and the body.

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.. phpmyadmin is good to have because if you're only reading and writing to specific tables in your scripts you only need to make the table once (same with database), then you can use it to edit tables and such instead of writing scripts to do anything you want to do with it.

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 to 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. or with the syntax highlighting it automatically creates links 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";


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);


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 (meaning it joins the strings together)

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>

ok, now in a php form all it does is get input from a user. in <form method="post" action="insert.php"> the method is the type of submission it is, and action is where it submits it to.
each field must have a name, so you know what variable is going to hold the information when its submitted to your php-handler page. eg: when you fill in "Test Title 1" in the title field, in your php page $_POST['title'] will be the contents of the title field... in this case "Test Title 1" .. for more html form fields and explanations, see webmonkey

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")) . ".gif"; //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.. and if you dont have the same amount of fields as what you're trying to insert, mysql will return an error
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..

i think i've covered a lot of stuff, if theres anything in peticular you need to know just ask

:


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

Terms of Use | Privacy Policy