...

View Full Version : help in a simple script please



Snitz
03-07-2007, 01:41 PM
I'm trying to build a small php/mysql script like www.little-thoughts.co.uk

so far I've done this!

insert_db.php

<?php
$con = mysql_connect("localhost","snitz","blood");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}mysql_select_db("thoughts", $con);$sql="INSERT INTO db
(quotes,name)
VALUES
('$_POST[quotes]','$_POST[name]')";if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}echo "Success!";

?>

index.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Thoughts</title>
</head>

<body>
<form action="insert_db.php" method="POST">
<label>
<div align="center">
<textarea name="quotes" cols="50" rows="20"></textarea>
<br />
<br />
name:
<input type="text" name="name" />
<br />
</div>
</label>
<div align="center">
<input type="submit" value="Submit" />
</div>
</form>
</body>
</html>

It's actually working, I can see the data submitted from phpmyadmin, but I'd like to improove it.
the database is 2 tables: quotes and name.
both text, not null, no primary key, no index... (I don't know how to handle mysql tables that much)

I would like to show the number of the posted thought/comment like little-toughts.co.uk please.
and a small search form on the index.php

can you help me please?

Inigoesdr
03-07-2007, 03:24 PM
Make another field in your table called `id`, make it INT, length 15, extra -> auto increment, and primary key.
ALTER TABLE `db` ADD `id` INT( 15 ) NOT NULL ;
ALTER TABLE `db` ADD INDEX ( `id` ) ;
And change your insert_db.php page to this:


<?php
$con = mysql_connect("localhost","snitz","blood");
if(!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("thoughts", $con);
$quotes = mysql_real_escape_string($_POST['quotes']);
$name = mysql_real_escape_string($_POST['name']);
$sql="INSERT INTO db (quotes,name) VALUES ('$quotes','$name')";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "Success! Thought ID: " . mysql_insert_id();
?>

Snitz
03-07-2007, 03:31 PM
Thanks!
That worked great!
Now I need a page to display random quotes or to start from the beggining or the end with [Next] & [Previous] Links!
And in that page I also want to show the quote with the name of the submitter and number of quote!

Just like www.little-thoughts.co.uk

Thanks again, I appreciate it!

Inigoesdr
03-07-2007, 03:33 PM
You're welcome ;)
Make sure you set the id field to auto increment after you set the key.

ALTER TABLE `db` CHANGE `id` `id` INT( 15 ) NOT NULL AUTO_INCREMENT;

Snitz
03-08-2007, 12:04 PM
I'm just wondering, is there any ready quoting script?
If I'm gonna build this all by myself, it's gonna take me a year or 2.
If I could get a custom script and tweak around the way I want, it would be much better.
With user register/login system, submitting quotes and stuff!

Snitz
03-08-2007, 12:34 PM
I don't think I'm gonna find such script so I'm gonna take the chance and build it myself, even if it's gonna be a mess!

So far I have 4 php files:
- config.php
- index.php
- insert_db.php
- quotes.php

The inset thoughts and everything is working properly but I need to know something, how can I show a random quote/thought in quotes.php ?

This is quotes.php

<html>
<title>Random Thought</title>
</html>
<?php
$mysqli = mysqli_connect("localhost", "snitz", "blood", "thoughts");

if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
} else {
$sql = "SELECT * FROM db LIMIT 1";
$res = mysqli_query($mysqli, $sql);

if ($res) {
while ($newArray = mysqli_fetch_array($res, MYSQLI_ASSOC)) {
$id = $newArray['id'];
$quotes = $newArray['quotes'];
$name = $newArray['name'];
echo"".$quotes." - <i>By ".$name."</i><br />Thought #".$id."<br/><br />";
}
} else {
printf("Could not retrieve records: %s\n", mysqli_error($mysqli));
}

mysqli_free_result($res);
mysqli_close($mysqli);
}
?>

If you can see

$sql = "SELECT * FROM db LIMIT 1";
How can I make it to show a random line?

And one more thing;
I made config.php to put the php/mysql connection in it and inside every page I'm adding

include('config.php');
But I couldn't do that in quotes.php coz I think the connection code is different, can you help in this please?
This is the code of config.php

<?php
$con = mysql_connect("localhost","snitz","blood");
if(!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("thoughts", $con);
?>

If I finish this step, then all I need to do is design a page for it.
Next step will be creating a registration forum and users profiles.

Thanks for the help guys!

Snitz
03-08-2007, 01:06 PM
I was able to find how to randomise the selection
I used

SELECT * FROM db ORDER BY RAND();
But now I'm having a hard time finding how to count how many entry I have inside the ID column in the db table.
Any help please.

Nightfire
03-08-2007, 01:44 PM
Use count() in the query

SELECT field1,field2, COUNT(id) FROM db

Snitz
03-08-2007, 02:35 PM
What's filed1 and field2?

This is how the code is looking but it's giving an error anyway.


$sql = "SELECT * FROM db ORDER BY RAND() LIMIT 1";
$sql2 = "SELECT * COUNT() FROM db";
$res = mysqli_query($mysqli, $sql);

if ($res) {
while ($newArray = mysqli_fetch_array($res, MYSQLI_ASSOC)) {
$id = $newArray['id'];
$quotes = $newArray['quotes'];
$name = $newArray['name'];
echo"".$quotes." - <i>By ".$name."</i><br />Thought #".$id."<br/><br />";
echo"".$sql2."";
}

Nightfire
03-08-2007, 02:52 PM
You shouldn't be retrieving all the fields from the table by using *, you should call them all singally by the field names. You need to put the fieldname of the id into count()

count(fieldnamewiththeidinhere)



$sql = "SELECT *,COUNT(id) FROM db ORDER BY RAND() LIMIT 1";
$res = mysqli_query($mysqli, $sql);

if ($res) {
while ($newArray = mysqli_fetch_array($res, MYSQLI_ASSOC)) {
$id = $newArray['id'];
$quotes = $newArray['quotes'];
$name = $newArray['name'];
echo"".$quotes." - <i>By ".$name."</i><br />Thought #".$id."<br/><br />";
echo $newArray['count(id)'];
}

Snitz
03-08-2007, 04:04 PM
Thanks mate, that worked!

I'm having another problem!
When I submit a text in the form I previously made, it's coming up like this when I do the echo command.


the dreams in which I\'m having are the best i\'ve ever had

How can I remove the slashes?

Nightfire
03-08-2007, 04:06 PM
Use stripslashes (http://php.net/stripslashes)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum