PHP+MYSQL Help

Penguin

Fledgling Freddie
Joined
May 11, 2005
Messages
375
Hey Folks,

I'm setting up a news system on my website using php and mysql and it's proving rather tricky, i'm hoping someone out there can help me ;)

Basically the user logs into a password protected page which contains a form with sevral fields for the user to fill in (Title, Date, Content, that kind of thing)

I'm rather new to PHP but after reading a gazillion tutorials and guides especially on PHP with MYSQL i came up with this:

Code:
   <form method="post" action="newsprocess.php" method="post">
    <table width="100%" border="0" cellspacing="0" cellpadding="0" name="memeberparent" >
    <tr><td bgcolor="#000000">
    <table width="100%" border="0" cellspacing="1" cellpadding="10">
    <tr bgcolor="#708090">
    <td bgcolor="#708090"><font face="verdana" size="2"><center>Title:</center></font></td>
 <td bgcolor="#708090"><font face="verdana" size="2"><input type="text" wrap="physical" name="title"></font></td>
    </tr>
    <tr bgcolor="#708090">
    <td bgcolor="#708090"><font face="verdana" size="2"><center>Time:</center></font></td>
    <td bgcolor="#708090"><font face="verdana" size="2"><input type="text" wrap="physical" name="datetime">
    <!-- PHP Time/Date -->
    <?php
    print date("l M dS, Y, H:i:s")
    ?></font></td>
    </tr>
    <tr bgcolor="#708090">
    <td bgcolor="#708090"><font face="verdana" size="2">Content:</center></font></td>
    <td bgcolor="#708090"><font face="verdana" size="2">
    <textarea rows="20" cols="60" wrap="physical" name="content">
    Enter News Here
    </textarea></center></font></td>
    </tr>
    <tr  bgcolor="#708090">
    <td bgcolor="#708090"><font face="verdana" size="2"><center>Submit:</center></font></td>
    <td bgcolor="#708090"><font face="verdana" size="2"><center>
    <input type="submit" value="Submit News"></form></center></font></td></tr></table>
    </center>
    </td>
    </tr>
    </table> 
    <br><br><br><br>
    </td>
    </tr>

The user clicks submit and moves onto newsprocess.php:

PHP:
<?php
    
    $title = $_POST['title'];
    $content = $_POST['content'];
    $datetime = $_POST['datetime'];
    
    $conn = mysql_connect("localhost", "USERNAME", "PASSWORD");
    //select db
    $select = mysql_select_db("DATABASENAME");
    //query
    $query = "INSERT INTO TABLENAME VALUES ('','$title','$datetime','$content')";
    
    mysql_close($conn);
    ?>

I did fill in the database name, username and password.

I've setup the database the table and the fields. Would what "type" the fields are perhaps be a problem?

I did test it, entered the data, clicked submit, all seemed well except my table remained empty :( This data would later be retrieved and displayed on the home page, if ofcourse i could get it to work ;)

Any help much appreciated.

- Penguin.
 

Chilly

Balls of steel
Joined
Dec 22, 2003
Messages
9,046
you have no function passing $query to your database. you need to do mysql_query($query); to actually send the query to the db.


/edit feh, so slow!
 

RandomBastard

Can't get enough of FH
Joined
Dec 28, 2003
Messages
1,318
You never actually perform the query.
You only create a variable called $query.

You need this line.

$result = mysql_query($query) or die("Query failed : " . mysql_error());

as well...


edit : yup too slow also you might want to place $query in
mysql_real_escape_string($query)
to prevent sql injection
 

Penguin

Fledgling Freddie
Joined
May 11, 2005
Messages
375
I love you guys, that was frustrating me so much. And wow that was a quick responce.

Penguin
 

Panda On Smack

Can't get enough of FH
Joined
Dec 22, 2003
Messages
1,030
with an insert statement do you have to list the fields first and then their values?

INSERT INTO TableName ('Field') VALUES ('FieldValue')
 

Jonty

Fledgling Freddie
Joined
Dec 22, 2003
Messages
1,411
Very helpful everyone, reputation bumped :D Good luck with the project, Penguin :)

Kind Regards
 

Penguin

Fledgling Freddie
Joined
May 11, 2005
Messages
375
Thanks Jonty, it's all going quite well, except the php/mysql side :p The MYSQL admin panel my host has is useless, it's just so slow, i gave up and used php to organize the table instead.

I've got another question, not sure if anyone know but i reckon it's worth a shot as i can't seem to find it anywhere :(

Does anyone know how to use MYSQL to find how many rows are in a particular table? I had this noted down before but i've lost it :( I was thinking of using this to display the latest news, unless anyone has a better way?

Thanks again,

Penguin.
 

Jonty

Fledgling Freddie
Joined
Dec 22, 2003
Messages
1,411
Hi guys

Just for future reference, the count function is great for efficiently counting rows, e.g.

Code:
SELECT COUNT(*) FROM `table_name`
Kind Regards

Jonty

P.S. The searchable MySQL Dev. Manual can be great for details, if a little overwhelming, much like the PHP Manual :)
 

Penguin

Fledgling Freddie
Joined
May 11, 2005
Messages
375
Hey Folks,

Today the MYSQL admin panels woken up a little so i was able to check and the form now works :D

Thanks for the help with that code Jonty.

My next task is getting the latest few entries to display on the home page, again, i've run into problems -

Heres what i've got so far:

PHP:
  <?php
  
  mysql_connect ('localhost' , 'USERNAME' , 'PASSWORD');
  mysql_select_db ("DATABASENAME");
  $count = mysql_query (SELECT COUNT * FROM TABLENAME);
  mysql_query ("SELECT * TABLENAME WHERE id=$count");
  
  ?>

I'm hoping that makes sence?
Is there another line i need to add which actually displays whats found?

Any help much appreciated,

Penguin.
 

Jonty

Fledgling Freddie
Joined
Dec 22, 2003
Messages
1,411
Hi Penguin

You can actually simplify that into just one query

Code:
$query = mysql_query("SELECT `column_names` FROM `table_name` ORDER `column_name` DESC LIMIT 5");
What this basically does is select information from your table (either column names or '*' for every column), ordered by a column name. This column name will mostly likely be the date or whatever you want to sort the information by. The 'DESC' keyword merely means descending (e.g. newest first, getting older, use 'ASC' for the opposite). The 'LIMIT' keyword limits the number of rows returned. Say you had 100 rows of information in your table, you wouldn't want all 100, so just change the limit number to however number of rows of information you need (I've used 5 just as an example). In this example we'd retrieve the last five entries in your database table.

Hope this helps.

Kind Regards
 

Jonty

Fledgling Freddie
Joined
Dec 22, 2003
Messages
1,411
Hi Penguin

An example of what you may be trying to do :) Say we have our news information in a database table which looks like:

Code:
id |   date   | headline
--------------------
1  | 2005 ... | Sample 
2  | 2004 ... | Another
3  | 2003 ... | Again
We could then use the following code to retrieve the five latest entries:

Code:
<?php
// Connect to the database
mysql_connect("database_location", "username", "password");

// Select the database
mysql_select_db("database_name");

// Retrieve the desired information from the database table
// In this instance, we select every piece of information from our database table, limiting it to the last five entries
$query = mysql_query("SELECT * FROM `table_name` ORDER BY `date` DESC LIMIT 5");

// Cycle through each row of information retrieved in our query and output appropriately
while ($row = mysql_fetch_assoc($query))
{
  // Take each row our query retrieved and print the following HTML for each entry
  printf('<h3><a href="mynewspage.php#%s" title="Read this news entry">%s</a></h3> <p>Written on %s.</p>'."\n\n",
    $row["id"],
    $row["headline"],
    $row["date"]
  );
}
What we are left with would be a page of five headings, each linking to a fictious page where each entry could be read. We also print out when the article was written.

Please note this isn't to be taken as perfect code, it doesn't feature any error checking etc. in case no database information can be found. It's just an example of how you may approach things.

Kind Regards
 

Penguin

Fledgling Freddie
Joined
May 11, 2005
Messages
375
Thanks Jonty, that will help alot. How i was planning on doing things was to display the latest news on the home page, kinda like this:

Title 1 - Date 1
---------------

Content1



Title2 - Date 2
--------------

Content 2


Title 3 - Date 3
---------------

Content 3

Using your help to ajust my current code i've now got:

PHP:
 <?php
 // Connect to the database
 mysql_connect("localhost", "username", "pw");
 
 // Select the database
 mysql_select_db("dbname");
 
 // Retrieve the desired information from the database table
 // In this instance, we select every piece of information from our database table, limiting it to the last five entries
 $query = mysql_query("SELECT * FROM table ORDER BY id ASC LIMIT 5");
 
 
 
 ?>

I've got an auto_increment field "id" which when each new news article is submitted automatically sets the id to the next number. I guess now i need to use "print" to actually place the information on the page?

Penguin.
 

Jonty

Fledgling Freddie
Joined
Dec 22, 2003
Messages
1,411
Hi Penguin

Once you have the information you need, courtesy of $query, you're absolutely right in that you just need to print it. Since you're collecting multiple rows of information you need that while (...) { ... } piece of code to cycle through every entry you've selected. What you do with the information from there is entirely down to you (in this instance, you would print what you wish to be displayed on your page by placing the appropriate code inside the while() loop). PHP's echo/print and printf functions are useful for this.

Tip of the day, to search for a function in the PHP manual, just go to http://www.php.net/ and suffix the web address with the function you're looking up, e.g. http://www.php.net/echo or http://www.php.net/mysql_query. You will then be redirected to the appropriate page of the manual, or a search of the manual will be performed if no such entry is found.

Kind Regards

Jonty

P.S. In your example the rows are being order by the ID in an ascending fashion, e.g. 1,2,3 which would put the oldest article first.
 

SheepCow

Bringer of Code
Joined
Dec 22, 2003
Messages
1,365
Did you know that "SELECT *" is evil?
http://www.parseerror.com/sql/select*isevil.html

Use the names of the fields when possible!

I see you want the date outputted, where is this coming from? If you're storing it in the database you should sort your query by date not by the id. That way if you insert an entry and set the date in the past it will still appear in the correct place.
 

Jonty

Fledgling Freddie
Joined
Dec 22, 2003
Messages
1,411
Very true, SheepCow, you're spot on with those points (of course :D). Sorry for not mentioning it, Penguin, must be slipping up in my old age :)

Kind Regards
 

Penguin

Fledgling Freddie
Joined
May 11, 2005
Messages
375
SheepCow said:
I see you want the date outputted, where is this coming from? If you're storing it in the database you should sort your query by date not by the id. That way if you insert an entry and set the date in the past it will still appear in the correct place.

Thanks again for the replys.

I'm using php to generate the date/time next to the form where this is entered and the user copys and pastes it in - It is being stored in the database but it's in the format "Tuesday Aug 16th, 2005, 12:31:29" So it wouldn't be very easy to sort it by date? I've got a field set to "auto_increment" and this is how it is sorted currently, seems to be working okay.



All seems well, i'm now using this code:
(If anyones interested :D)
PHP:
   <?php
   // Connect to the database
   mysql_connect("localhost", "user", "password");
   // Select the database
   mysql_select_db("database");
   
   $sql = "SELECT * FROM table ORDER BY id DESC LIMIT 5";
   $result = mysql_query($sql);
   $query_row = mysql_fetch_assoc ($result);
   $query_numrows = mysql_num_rows ($result);
   ?>

followed by this to display the results:

PHP:
   <?php for($i=1;$i <= $query_numrows;$i++)  {  ?>
   <table width="100%" border="0" cellspacing="1" cellpadding="0">
   <tr>
 <td><font size="2"> <?php echo $query_row [title]; ?>,<?php echo $query_row [datetime]; ?> </font></td>
   <tr>
   <td><fontsize="2"> --------------------
   <tr>
   <td><font size="2">  <?php echo $query_row [content]; ?>  </font></td> 
   </tr>
   <tr>
   <td><fontsize="2"><center><br><br><br> ____________________ </center></td>
   </tr>
   </table>
   <br><br><br>
   <?php $query_row = mysql_fetch_assoc ($result);
   
   }
   ?>

And it's working great! Thanks again for the help folks,

Penguin.
 

SheepCow

Bringer of Code
Joined
Dec 22, 2003
Messages
1,365
Aha.

You may find it better to store the date in a field of type DATE (or DATETIME), that way you can sort in MySQL.

To convert your date you could use strtotime() to convert from a string to the timestamp (and store as an INT in the database), or (harder) use a regular expression and mktime()

You should use ' or " around the names in the array, e.g. $query_row["moo"], otherwise you'll be having error notices (these are rarely set to be displayed as they're seen as pedantic by some people) as PHP will look for the constant moo before assuming you meant the string "moo".

Also, a "neater" (opinion) way of looping round results from a db is:

PHP:
<?php

while($query_result = mysql_fetch_assoc($result))
{
    ....
}

?>
 

Users who are viewing this thread

Top Bottom