Basics of extracting data from MySQL using PHP

pasting data into HTML page at any point

[chrisdevelopment]

Hi I am new and have only just got PHP and MySQL, I have a web page in which I need to extract data and photos in various places in the web page.The data is updated daily on the server so the information displayed would change automatically on the page when the web page was shown.

I have seen Mysql coding that is put into the HTML page to connect but I am not happy with this as it would entail showing the password and usernames which anyone could see. Can anyone help in any guidance on how to start, and how to extract using PHP. It may be childs play to many, but it does look daunting, and not knowing where to start means I am a loss.

[mcibor]

You need to extract data from the page, or insert data to the page? I think the latter.

you need to have two levels of access:
1st level for anonymous user, just to look and contemplate
2nd level for owner/root to insert new pictures, etc.

For the 1st level just extract the photos from database

<?PHP
$query = "SELECT name, link FROM db.photos ORDER BY id DESC LIMIT 0, 20" // this will select name and link from table "photos" in database db, ordered by creation (first is the newest) and only 20 starting from the 0th (first already ordered)

if($result = mysql_query($query) or die(mysql_error()))
{
if(mysql_num_rows($result))
{
$row = mysql_fetch_assoc($result);
//now in $row['name'] you have a name of the photo, in $row['link'] a link to it.
}
}

The second part is harder. First you need login script. THere are many of them everywhere. Then store the logged user in session, then allow him to add to mysql

INSERT INTO db.photos(name, link) VALUES('$name', '$link')

I don't know if you want him to be able to erase photos. Better in my opinion would be creating a new column: status INT DEFAULT '1' (if 1 not erased, if 0 then erased)

then select would be:

SELECT name, link FROM db.photos WHERE status = '1' ORDER BY id DESC LIMIT 0, 20

And erasing would be

UPDATE db.photos SET status = 0 WHERE id = '$id';//id is a unique row value

Table photos would look like:

CREATE TABLE photos( id INT NOT NULL auto_increment, status INT DEFAULT '1', name VARCHAR(50), link VARCHAR(50), PRIMARY KEY(id));

Hope it clears the procedure a bit.
Best regards
Michal Cibor

[Timotheos]

Hi chrisdevelopment and Welcome to WebmasterWorld!

There's a library [webmasterworld.com] here with a lot of good info. You'll want to check out the thread on "Basics of extracting data from MySQL using PHP" [webmasterworld.com].

The mySQL username and password are in the PHP script but this is not shown to the end user. You can check the source in your browser to prove it to yourself.

Tim

[coopster]

Welcome to WebmasterWorld, chrisdevelopment.

Even the title matches ;) Basics of extracting data from MySQL [webmasterworld.com]

The PHP Forum Library [webmasterworld.com] is loaded with topics to help get you going.

man that Timotheos is fast

[chrisdevelopment]

Thanks to everybody for the help I am overwelmed at the fast response and the info. Most operations to the database will be done through PHPMyAdmin, it is just really pulling the info from the database to display it on the web page for the viewer to see.

I will have a look at all the suggestions tomorrow, bit late now at 11.40 Many thanks to all