Web Hosting Talk






PDA

View Full Version : help in a simple script please

Snitz
03-07-2007, 10:18 AM
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?
Saeven
03-08-2007, 01:58 AM
Your table needs an auto-increment column. MySQL does the rest for you :)
Burhan
03-08-2007, 02:24 AM
You should also escape your SQL queries. Try mysql_real_escape_string() (http://php.net/mysql-real-escape-string).
timcordova
03-08-2007, 03:46 AM
You should try to do a google search on mysql tutorials.
Snitz
03-08-2007, 08:40 AM
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, 09:09 AM
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.
isurus
03-08-2007, 10:26 AM
http://www.tizag.com/mysqlTutorial/mysqlcount.php

( This was the first result back from a quick spot of googling for mysql count (http://www.google.com/search?as_q=mysql%20count) )
Snitz
03-08-2007, 10:41 AM
I can't seen to figure it out... can you please help in this?

This is the code of 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 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 />";
}
} else {
printf("Could not retrieve records: %s\n", mysqli_error($mysqli));
}
mysqli_free_result($res);
mysqli_close($mysqli);
}
?>
Snitz
03-08-2007, 12:04 PM
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?
isurus
03-08-2007, 12:39 PM
Use stripslashes()

<?php

$bob="Bob\'s apple.";
echo $bob;
echo stripslashes( $bob );
?>
Snitz
03-09-2007, 10:08 AM
Use stripslashes()

<?php

$bob="Bob\'s apple.";
echo $bob;
echo stripslashes( $bob );
?>
Yes, but I'm using mysql, variables and arrays.
What I have isn't a normal variable like this one!

This is the code on insert_db.php
<?php
include('includes/config.php');
$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();
?>

and this is the code on index.php
<?php
include('includes/config.php');
// include('includes/quotes.php');
// include('includes/count.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 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 />";
}
} else {
printf("Could not retrieve records: %s\n", mysqli_error($mysqli));
}
mysqli_free_result($res);
mysqli_close($mysqli);
}
?>

As you can see it's a little bit more complicated, and I hope u'd help me understand how do I insert the stripslashes into this kind of code.

Thanks for the help!
Saeven
03-09-2007, 01:25 PM
I'll let others carry on, but I'll post a word of advice regarding RAND, which is a deceivingly expensive operation. It should NEVER be used in a system where users can actuate the mechanism, since it scans ALL rows to generate the random order. If you have 34000 rows in your table, it will run all 34000 rows before returning a single row, which is inexcusable.

To check this, do:

EXPLAIN SELECT * FROM table ORDER BY RAND() LIMIT 1;

You'll see that no key is being used, and that all of your rows are being scanned.

Instead, use something like this, which will only scan one row at random:


SELECT * FROM
(SELECT FLOOR( RAND() * ( SELECT MAX( id ) FROM table) ) AS id) AS X
LEFT JOIN table
ON X.id = topics.id
LIMIT 1;


It might seem more complex, but it's saving your MySQL server a huge ton of work, and will be much faster. The first query takes .2251 seconds on a table with ~15000 rows, and the second .0451 on the same machine.

There's one gotcha, you have to pack your keys. In other words, if you are going to delete entries, there always has to exist a logical linear sequence to your row ids (your auto_increment column). You'll otherwise get NULL entries in your right hand member.

Good luck!
Alex