My gurus, please help with my mysql

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Caroline
Forum Commoner
Posts: 25
Joined: Thu Jan 30, 2003 1:38 pm

My gurus, please help with my mysql

Post by Caroline »

My dear gurus,

i'm a graphic designer trying to create a site giving Photoshop, CorelDraw, 3DMax... tutorials. But for now, i got some troubles on creating, controlling, querying mysql database (In this field, i'm just a newbie).
If you have some spare times, please help.

First, the site requests are

- Adding, editing, deleting (Hope i won't delete any) tutorials to database
- Displaying tutorials to site visitors
- Allowing people to submit tutorials to my site so that we can share them
- A protected admin area for me to check new submitted tutorials, adding, editing my tutorials <<< I may use .htaccess if there is no better solution

As simply as that!

Consider i have only one section of tutorials (e.g. Photoshop), then i create one table for them

Code: Select all

CREATE TABLE tutorial_photoshop (
id int not null auto_increment,
author_name varchar(50),
author_email varchar(),
postdate DATE,
tutorial_title varchar(255),
short_description TEXT,
tutorial MEDIUMTEXT,
primary key (id), // I'm still wondering what PRIMARY & UNIQUE are and what they are for.
unique id (id)
);
Yes, that's the table i have created and below is the tutorials.php

Code: Select all

<?

// My server host support short_tag, so i don't need <?php

// Connect to database

mysql_connect('db_host','db_user','db_pass'); // What should be here to print out somthing if error?
mysql_select_db('db_name');

// Do some stuffs

// As i only need 5 records displays with full information, i do this

$result = mysql_query("SELECT * FROM tutorial_photoshop ORDER BY DATE() LIMIT 5");

echo "<b>Newest Tutorials</b>";

while($row = mysql_fetch_array($result)) &#123;
echo "<a href="show_tutorial.php?id=".$row&#1111;id].""><b>$row&#1111;tutorial_title]</b></a><br>";
echo "By <b><a href='mailto: $row&#1111;author_email]'>$row&#1111;author_name]</a></b> on $row&#1111;postdate]";
echo "<p>$row&#1111;short_description]";
echo "<p>$row&#1111;tutorial]";
&#125;


?>

<?

// We need some more tutorials if people want them
// They are not the latest but the next 5

$result = mysql_query("SELECT * FROM tutorial_photoshop ORDER BY DATE() LIMIT 6,10");

echo "<p><b>New Tutorials</b>";

while($row = mysql_fetch_array($result)) &#123;
echo "<a href="show_tutorial.php?id=".$row&#1111;id].""><b>$row&#1111;tutorial_title]</b></a> by $row&#1111;author_name]";
&#125;

?>
<p><a href='displayall.php'>All tutorials</a>
The show_tutorial.php is here

Code: Select all

<?

mysql_connect('db_host','db_user','db_pass');
mysql_select_db('db_name');

$result = mysql_query("SELECT * FROM tutorial_photoshop WHERE id=$id");
$row = mysql_fetch_array($result); 

echo "<b>$row&#1111;tutorial_title]</b><br>";
echo "By <a href='mailto: $row&#1111;author_email]'>$row&#1111;author_name]</a> on $row&#1111;postdate]";
echo "<p>$row&#1111;short_description]";
echo "<p>$row&#1111;tutorial]";

?>
The add_tutorial.php like this

Code: Select all

<?

// Inserting data into table

if ($submit) &#123;

mysql_connect('db_host','db_user','db_pass');
mysql_select_db('db_name');

$result = mysql_query("INSERT INTO tutorial_photoshop VALUES ('','$author_name','$author_email','$postdate','$tutorial_title','$short_description','$tutorial') ");

echo "Thank you! Tutorial added!";
&#125;

?>

<?

// Print out the form to supply data

echo "<form method=post action='$PHP_SELF'>";
echo "<input type=text name=author_name> Author Name<br>";
echo "<input type=text name=author_email> Author Email<br>";
echo "<input type=hidden name=postdate value="$date('d-M-Y')">"; // This may be error. Confused!
echo "<input type=text name=tutorial_title> Tutorial Title<br>";
echo "<input type=text name=short_description> Short Description";
echo "<p>Content of the tutorial<br><textarea name=tutorial></textarea>";
echo "<input type=submit name=submit value=Send>";
?>
Some questions

- Do those codes correct. i mean if i use those codes, are there any errors?
- Can my script support line break (The <p> and <br>) automatically? If not, how to?
- Can my script support HTML? I mean if i supply HTML into the textarea, do they work when displayed in show_tutorial.php
- What should I do to make the scripts support BBCode insteads of HTML
- If i want to stop people hacking my site, how can i stop them supply HTML into the add_tutorial.php
- Does any better solution you want to show me?
- Any comments or suggestions?

I know this topic will take you lots of times. Thus, if you are not free, answer me later, i will wait.
And if you answer me soon, that will be more than happiness.

Thanks for reading

Caroline
bionicdonkey
Forum Contributor
Posts: 132
Joined: Fri Jan 31, 2003 2:28 am
Location: Sydney, Australia
Contact:

Post by bionicdonkey »

a few little things...
tutorials.php:

Code: Select all

mysql_connect('db_host','db_user','db_pass'); // What should be here to print out somthing if error? 
  mysql_connect('db_host','db_user','db_pass') or die(mysql_error());
ORDER BY DATE() ????
to my knowledge you need to orderby a field name (this looks like a function) but i may be wrong.
when echoing arrays non variable elements should be in '' or "" e.g. $row['element']

also...
html works fine...u can echo it in php or close the php tag things (<? ?>) and use html as normal.
a suggestion about db connection, change mysql_connect to mysql_pconnect and aslo put it in a seperate file then just require("connection.php"); when needed.
to stop the html adding bit lookup strip_tags() function in the php manual

hope this helps
Caroline
Forum Commoner
Posts: 25
Joined: Thu Jan 30, 2003 1:38 pm

Post by Caroline »

Yes, i will surely use a separate file (config.php) for db connection when making real scripts as they can share one connect file.

I love to order the tutorials by the day they were submitted, but don't know how to, and thus i use ORDER BY DATE() (even i know it may be wrong)

What's different between mysql_connect and mysql_pconnect :?:
bionicdonkey
Forum Contributor
Posts: 132
Joined: Fri Jan 31, 2003 2:28 am
Location: Sydney, Australia
Contact:

Post by bionicdonkey »

mysql_pconnect -- Open a persistent connection to a MySQL server

i strongly suggest getting the php manual...u can d/l it from http://www.php.net

in my opinion the best way to orderby date is to store the date as a unix timestamp (time() function). the in your select statment ORDER BY postdate ASC...also the datatype for unix timestamp in the db should probably be INT of length 14
Caroline
Forum Commoner
Posts: 25
Joined: Thu Jan 30, 2003 1:38 pm

:(

Post by Caroline »

Sorry for this truth

I do have downloaded all the documentations from php.net and mysql.com together with many tutorials about php around many sites. But as i'm neither an English speaker nor a PHP coder, i can understand such documentations much.

That's why i came here, join this forum of gurus and hope you can spend sometimes on helping me.

The Order by postdate Desc is a good idea and i will use it.

How about the others, my gurus?
bionicdonkey
Forum Contributor
Posts: 132
Joined: Fri Jan 31, 2003 2:28 am
Location: Sydney, Australia
Contact:

Post by bionicdonkey »

i just remembered, i have a tutorials module in my web site. have a look and let me know if have any questions or would like to see the code
Caroline
Forum Commoner
Posts: 25
Joined: Thu Jan 30, 2003 1:38 pm

Post by Caroline »

I can't find the tutorial you mentioned on your site's tutorial section.

But the Unix fun is surely funny ;) I like them!
bionicdonkey
Forum Contributor
Posts: 132
Joined: Fri Jan 31, 2003 2:28 am
Location: Sydney, Australia
Contact:

Post by bionicdonkey »

i was talking about the tutorials section itself...ther is also a admin modile for the section aswell but you won't be able to see it. the code i used may help you. if you want the code let me know and i'll send it to you
Caroline
Forum Commoner
Posts: 25
Joined: Thu Jan 30, 2003 1:38 pm

Great thanks for that

Post by Caroline »

Please, mail me your code or simply give me a download link.

Thanks in advanced
Caroline
Forum Commoner
Posts: 25
Joined: Thu Jan 30, 2003 1:38 pm

Thanks all!

Post by Caroline »

Special thank to the TRG News script from Underground, i've learned so much and make my scripts work now!
Also, i would like to thank bionicdonkey for spending times on helping me!

Thanks all!
Post Reply