Page 1 of 1

My gurus, please help with my mysql

Posted: Thu Jan 30, 2003 1:38 pm
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

Posted: Fri Jan 31, 2003 3:53 am
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

Posted: Fri Jan 31, 2003 4:05 am
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 :?:

Posted: Fri Jan 31, 2003 4:21 am
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

:(

Posted: Fri Jan 31, 2003 4:32 am
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?

Posted: Fri Jan 31, 2003 4:41 am
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

Posted: Fri Jan 31, 2003 4:51 am
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!

Posted: Fri Jan 31, 2003 5:00 am
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

Great thanks for that

Posted: Fri Jan 31, 2003 5:03 am
by Caroline
Please, mail me your code or simply give me a download link.

Thanks in advanced

Thanks all!

Posted: Sat Feb 01, 2003 1:21 am
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!