PHP & MySQL - Storing & Display Data

[Jamie]

I am trying probably the most simple thing that can be done with PHP & MySQL together.

That is, allow for the visitor to fill out a form, have the contents stored in a database and then have the contents of the database displayed on a page.

I have followed two tutorials, completing them both on my local machine with phpmyadmin and both have received parse errors. It certainly isn't a mistake by me because all I am doing is copying and pasting the code it gives me.

So, would someone be so kind to show me the SQL needed for the database and the PHP needed for both submit_message.php, store_message.php and display_message.php or point me to a tutorial they know that works so I can see if it is me who is making a mistake.

[Alex]

First make the table:

mysql_query("CREATE TABLE stuff (
id int(9) NOT NULL auto_increment,
content TEXT NOT NULL,
PRIMARY KEY (id))") or die ("NOooo, I died " . mysql_error()));


submit_message.php:

<form method="post" action="store_message.php">
<textarea name="stuff"></textarea><br />
<input type="submit" value="Store" />
</form>


store_message.php:

<?php

$c = mysql_connect("location","user","pass");
if (!$c) die("Crap.. not again " . mysql_error());
mysql_select_db(database_name,$c) or die(" Grrrr " . mysql_error());

if($_POST["stuff"]) {
$stuff = $_POST["stuff"];
$store = "INSERT INTO stuff (content) VALUES ('$stuff')";
mysql_query($store) or die("This isn't working well..." . mysql_error());

echo "Success!!! Data added to database!";
header('Refresh: 3; URL=display_message.php');

}
?>


display_message.php:

<?php
$c = mysql_connect("location","user","pass");

if (!$c) die("Crap.. not again " . mysql_error());
mysql_select_db(database_name,$c) or die(" Grrrr " . mysql_error());

$get = mysql_query("SELECT * FROM stuff") or die("Mmm pie:" . mysql_error());

while ($row = mysql_fetch_row($get)) {
echo "<h4>Stuff you stored:</h4>\n";
echo "<p>$row[1]</p> ";
}
?>



Save each file and replace the username, pass, location and the database name for the connection. I haven't tested it but it should work :)

[Jamie]

Thanks Alex, that is basically what I was trying but I was getting errors. I will try it and report back. Please could you confirm one thing, if I am using this on my local apache installation, I can simply get rid of the following everywhere it appears in the script:


,"user","pass"


Is this correct?

[Jamie]

Alex, I am getting an error when I try to create the table using the SQL...


Error

SQL-query :

mysql_query(

"CREATE TABLE stuff ( id int(9) NOT NULL auto_increment, content TEXT NOT NULL, PRIMARY KEY (id))"
) or die(
"NOooo, I died ".mysql_error(
)
)
)

MySQL said:


You have an error in your SQL syntax near 'mysql_query("CREATE TABLE stuff ( id int(9) NOT NULL auto_increment, content TEX' at line 1

[Alex]

I just correted an error in the code sorry but that, try copying it again, and make sure you replace the database name.

I'm not sure if you are allowed to do that. You should have a username and password on your mysql installation , I think the default is 'root' look in phpmyadmin under 'Privileges' in the home page. just leave them like this if not:

mysql_connect("localhost","","");

[Alex]

If you are pasting the code straight in the phpmyadmin SQL query textarea you only need to paste this:

CREATE TABLE stuff (
id int(9) NOT NULL auto_increment,
content TEXT NOT NULL,
PRIMARY KEY (id))

[Jamie]

Sorry to be such a pain Alex but I am still getting the error when copying and pasting the SQL in to phpmyadmin.


SQL-query :

mysql_query(

"CREATE TABLE stuff ( id int(9) NOT NULL auto_increment, content TEXT NOT NULL, PRIMARY KEY (id))"
) or die(
"NOooo, I died ".mysql_error(
)
)
)

MySQL said:


You have an error in your SQL syntax near 'mysql_query("CREATE TABLE stuff ( id int(9) NOT NULL auto_increment, content TEX' at line 1

[Alex]

Paste the Exact code below:

CREATE TABLE stuff (
id int(9) NOT NULL auto_increment,
content TEXT NOT NULL,
PRIMARY KEY (id))

[sonicgroup]

You're pasting too much. Only paste the portion in double quotes (starting with CREATE and ending with (id))).

[Jamie]

That worked, thanks Alex.

[Alex]

No problem :)

[Jamie]

Very very nearly there Alex, it works fine, everything is stored and displayed as needed, but, on store_message.php when I have submitted the form this appears:


Success!!! Data added to database!
Warning: Cannot add header information - headers already sent by (output started at c:\phpdev\store_message.php:12) in c:\phpdev\store_message.php on line 13


I am not using a username or password because when I did it didn't work so I assumed that when using it on a local version of apache you dont need them. I wouldn't have thought the above error was a user/pass related thing though.

[Alex]

Arghhh output buffering, forgot about that, try:


store_message.php:

<?php
ob_start(); // This was needed..
/* Turns output buffering on so only headers go, still trying to understand it further myself : ) */
$c = mysql_connect("location","user","pass");
if (!$c) die("Crap.. not again " . mysql_error());
mysql_select_db(database_name,$c) or die(" Grrrr " . mysql_error());

if($_POST["stuff"]) {
$stuff = $_POST["stuff"];
$store = "INSERT INTO stuff (content) VALUES ('$stuff')";
mysql_query($store) or die("This isn't working well..." . mysql_error());

echo "Success!!! Data added to database!";
header('Refresh: 3; URL=display_message.php');

}
?>


It should get rid of the error. :)

[Jamie]

Bingo! <Jamie dances around the room in joy and hands Alex a beer or two> Thanks a lot buddy, I owe you one.

[Alex]

It's all good ;)

[Jamie]

I will keep you updated Alex with what I use the script for, in its simpliest terms it is for a blog, I decided I wanted to be able to implement the script in to the design rather than using something such as word press that meant having to implement the design in to the script.

[Jamie]

Well, it is 2:38am and I have been messing with this for around 4 hours now and I have now finally figured out how to display more than one value.

So, the next stage is style. How would I go about styling the values and putting them where I want them. At the moment they are just in one line, seperated by a comma.

[Marble]

Once you get the values from the database, then you just echo them into html...

for example - if you have 2 values in the database, id & name:

[syntax:3c60b42102="php"]
while ($dbValues = mysql_fetch_assoc($linky)) {
echo '<p class="someclassname">'.$dbValues['id'].' :: '.$dbValues['name'].'</p><br />';
}
[/syntax:3c60b42102]

That would loop the result and for each entry in that table will display the values of id and name between html... Once you have the values if they are looped - use a loop or else if its just one row then you just display the variables just like any array variables in php...

http://ca.php.net/manual/en/function.mysql-fetch-assoc.php

*edit* there are no php BBcode tags...
Mod Edit: Use [ syntax="php"] and [ /syntax] for highlighting PHP ;)

[sonicgroup]

As Marble says, when you get your values back, loop over them and then echo them as you need them.

[Jamie]

Thanks guys, it is 4:20am here and the sun is about to rise in a couple of hours, I have now been up and sat here for 18 hours. If I don't do it in the next 30 min or so I will do it tomorrow evening when I wake up. ;)

[Jamie]

Marble, could you go through what each bit of the code you posted above does.

[syntax:10ffbaccd7="php"]
while ($dbValues = mysql_fetch_assoc($linky)) {
echo '<p class="someclassname">'.$dbValues['id'].' :: '.$dbValues['name'].'</p><br />';
}
[/syntax:10ffbaccd7]

For starters what is $dbValues? Is this just a made up variable?

What is $linky? Database name, table name?

[Alex]

Well $linky is the equivalent of $get in:

$get = mysql_query("SELECT * FROM stuff") or die("Mmm pie:" . mysql_error());


That selects everything fro the table 'stuff' and stores it into variable called $get.

Now you want to show each value of the variable $get

"mysql_fetch_row($get)" takes the values of $get and puts them into an array.

Then to show Each value of the array you use a "while()" loop:

So $row[0] contains the ID for each of the stored data.
$row[1] contains the stored data itself.

Echoing $row[1] inside the while() loop will display all the data stored in the table.


while ($row = mysql_fetch_row($get)) {
echo "


<div style="background : red; font-size : 12px; float : left; color : white;">$row[1]</div>


";
}


Style it however you want. You can also simply include display_message.php in any page you want using:

<?php
include('display_message.php');
?>

[Marble]

mysql_fetch_row(); and mysql_fetch_assoc(); return the same data, except the latter uses indexes and the former uses column names... just to point that out.. its a preference, imo... I prefere associative arrays as $var['user_id'] means more to me than $var['0'] for example... if the first column was 'user_id' in your user table...

But one critical thing to remember is when dealing with databases is ALWAYS screen your data before and after you insert it into your database... if its supposed to be a single digit number, then make sure only a single digit number is getting inserted into the database. Strip any tags and any back-ticks or quotes... SQL injection is a fairly simple hack and a few steps can prevent it...

[Jamie]

Alex, that is all well and good but wouldn't styling $row[1] just style every single value.

I have 4 fields in my form, Name, Email, Subject, Comments

At the moment when I submit the form it outputs the results as follows:

My Name, My Email, My Subject, My Comments

Notice they are all on the same line, seperated by a comma.

So, what would it take to insert My Name at the top of the page and My Comments at the bottom of the page, both with different styles?

Maybe they have to be seperated in to their own rows, how would this be done?

Here is what I have...

submit_entry.php

<form method="post" action="store_entry.php">
Name*<br />
<input type="text" name="name" value="Jamie Harrop"><p />

Email*<br />
<input type="text" name="email" value="jamieharrop14@hotmail.com"><p />

Subject*<br />
<input type="text" name="subject" size="50" value="Subject"><p />

Entry*<br />
<textarea cols="50" rows="12" name="comments">Message</textarea><p />

<input type="reset" value="Clear"> <input type="submit" value="Post Entry">
</form>


store_entry.php
[syntax:ead6f810e4="php"]
<?php
ob_start(); // This was needed..
/* Turns output buffering on so only headers go, still trying to understand it further myself : ) */
$c = mysql_connect("localhost","","");
if (!$c) die("Crap.. not again " . mysql_error());
mysql_select_db(blogn,$c) or die(" Grrrr " . mysql_error());

if($_POST["name"]) {
$name = $_POST[name];
$email = $_POST[email];
$subject = $_POST[subject];
$comments = $_POST[comments];
$store = "INSERT INTO entries (name) VALUES ('$name, $email, $subject, $comments')";
mysql_query($store) or die("This isn't working well..." . mysql_error());

echo "Success!!! Data added to database!";
header('Refresh: 3; URL=/index.php');

}
?>
[/syntax:ead6f810e4]

index.php
[syntax:ead6f810e4="php"]
<?php
$c = mysql_connect("localhost","","");

if (!$c) die("Crap.. not again " . mysql_error());
mysql_select_db(blogn,$c) or die(" Grrrr " . mysql_error());

$get = mysql_query("SELECT * FROM entries") or die("Mmm pie:" . mysql_error());

while ($row = mysql_fetch_row($get)) {
echo "
<h4>Stuff you stored:</h4>\n
";
echo "<p>$row[1]</p> ";
}
?>
[/syntax:ead6f810e4]

[Jamie]

CREATE TABLE entries (
id int(9) NOT NULL auto_increment,
name TEXT NOT NULL,
email TEXT NOT NULL,
subject TEXT NOT NULL,
comments TEXT NOT NULL,
PRIMARY KEY (id))


That is the sql I used to set up the table.

[Marble]

Well if you had a page:



< html header stuff >

<body>
<h1><?php echo $var['Name']; ?></h1>
<p id="first-paragraph"><?php echo $var['somedata']; ?></p>
</body>



make sense? The data is nothing but variables now... so put it anywhere you want on the page.. if you are just returning a single row - make sure you are in your code (SQL) and then no need for a loop. Your mysql_fetch_row or mysql_fetch_assoc(); will return some array variables you can use anywhere on your page....

[Jamie]

I have just tried that Marble but it didn't work, nothing appeared. I always thought that by doing it that way it would only display data straight after the form has been submitted and it won't store it, for example, on a thank you page after submitting a contact form, such as "Thank you <name>"

[Alex]

store_entry.php should look more like this:

[syntax:4b07e83402="php"]
<?php
ob_start();
$c = mysql_connect("localhost","","");
if (!$c) die("Crap.. not again " . mysql_error());
mysql_select_db(blogn,$c) or die(" Grrrr " . mysql_error());

if($_POST["name"] || $_POST["comments"] ) {
$name = $_POST["name"];
$email = $_POST["email"];
$subject = $_POST["subject"];
$comments = $_POST["comments"];
$store = "INSERT INTO entries (name,email,subject,comments) VALUES ('$name', '$email', '$subject', '$comments')";
mysql_query($store) or die("This isn't working well..." . mysql_error());

echo "Success!!! Data added to database! Page refreshing now...";
header('Refresh: 3; URL=/index.php');

}
?>
[/syntax:4b07e83402]

and index.php:

[syntax:4b07e83402="php"]
<?php
$c = mysql_connect("localhost","","");

if (!$c) die("Crap.. not again " . mysql_error());
mysql_select_db(blogn,$c) or die(" Grrrr " . mysql_error());

$get = mysql_query("SELECT * FROM entries") or die("Mmm pie:" . mysql_error());

echo "
<h4>Stuff you stored:</h4>
<div style=\"StyleItHere\">
\n
";
//if you put the style before and after the while loop it will only be echoed once.

while ($row = mysql_fetch_row($get)) {
echo "$row[1]<br />\n"; //Name
echo "$row[2]<br />\n"; //Email
echo "$row[3]<br />\n"; //Subject
}
echo "</div>";
//Note this will have a single div style for every entry for the name, email and subject. Create anoher while() loop for the comments below.

echo "<div style=\"comments style\">";
while($row2 = mysql_fetch_row($get)) {
echo "$row2[4]<br />\n"; //Comments
}
echo "</div>";
?>
[/syntax:4b07e83402]


//EDIT : Corrected Syntax..
//Edit 2 :

VALUES ('$name', '$email', '$subject', '$comments')";

each variable needs quotes

[Jamie]

Thanks Alex. I actually tried something like this this morning but I was getting an error, I am getting the same error with your example.


This isn't working well...Column count doesn't match value count at row 1


This comes after I have submitted the form.

[Alex]

Look at my last edited post.

On second thought, you Will need to echo a style for each looped comment, the above code for index.php will display the comments separately...

[Jamie]

Why is PHP so complicated? ;) I greatly appreciate all the help you are giving me guys, just a little while longer. :)

For some reason the styles don't see to be working either. All the <div style="style here"> apart from the first one are dropping below all the outputted content therefore having no effect.

This is the output:


<h4>Stuff you stored:</h4>
<div style="StyleItHere">
Jamie Harrop, jamieharrop14@hotmail.com, Subject, Message<br />
</div><div style="color: #000;"></div>
<div style="color: #000;">


</div><div style="comments style"></div> </div>


The PHP for this:


<?php
$c = mysql_connect("localhost","","");

if (!$c) die("Crap.. not again " . mysql_error());
mysql_select_db(blogn,$c) or die(" Grrrr " . mysql_error());

$get = mysql_query("SELECT * FROM entries") or die("Mmm pie:" . mysql_error());

echo "
<h4>Stuff you stored:</h4>
<div style=\"StyleItHere\">
\n
";
//if you put the style before and after the while loop it will only be echoed once.

while ($row = mysql_fetch_row($get)) {
echo "$row[1]<br />\n"; //Name
}
echo "</div>";

echo "<div style=\"color: #000;\">";
//if you put the style before and after the while loop it will only be echoed once.

while ($row = mysql_fetch_row($get)) {
echo "$row[2]<br />\n"; //Email
}
echo "</div>";

echo "
<div style=\"color: #000;\">
\n
";
//if you put the style before and after the while loop it will only be echoed once.

while ($row = mysql_fetch_row($get)) {
echo "$row[3]<br />\n"; //Subject
}
echo "</div>";

//Note this will have a single div style for every entry for the name, email and subject. Create anoher while() loop for the comments below.

echo "<div style=\"comments style\">";
while($row2 = mysql_fetch_row($get)) {
echo "$row2[4]<br />\n"; //Comments
}
echo "</div>";
?>

[Jamie]

I think we are nearly there Alex.

[syntax:5d8f611399="php"]
<?php
$c = mysql_connect("localhost","","");

if (!$c) die("Crap.. not again " . mysql_error());
mysql_select_db(blogn,$c) or die(" Grrrr " . mysql_error());

$get = mysql_query("SELECT * FROM entries") or die("Mmm pie:" . mysql_error());

echo "<div style=\"StyleItHere\">\n";
//if you put the style before and after the while loop it will only be echoed once.

while ($row = mysql_fetch_row($get)) {
echo "$row[1]<br />\n"; //Name
echo "$row[2]<br />\n"; //Email
echo "$row[3]<br />\n"; //Subject
echo "$row[4]<p />\n"; //Comments
}
echo "</div>";
//Note this will have a single div style for every entry for the name, email and subject. Create anoher while() loop for the comments below.
?>
[/syntax:5d8f611399]

Could you show me how to seperate all 4 of the below values that will then enable me to have a style for each one because I am totally lost.

[syntax:5d8f611399="php"]
echo "$row[1]<br />\n"; //Name
echo "$row[2]<br />\n"; //Email
echo "$row[3]<br />\n"; //Subject
echo "$row[4]<p />\n"; //Comments
[/syntax:5d8f611399]

[Alex]

put this style in the document Head as usual and change it to whatever you want:



.name {color : #000}
.email {color : #222}
.subject {color : #555}
.comments {color : red}

.container {background : #ffffff}



the php:
[syntax:802aaa9e47="php"]
<?php
$c = mysql_connect("localhost","","");
if (!$c) die("Crap.. not again " . mysql_error());
mysql_select_db(blogn,$c) or die(" Grrrr " . mysql_error());
$get = mysql_query("SELECT * FROM entries") or die("Mmm pie:" . mysql_error());
echo "
<h4>Stuff you stored:</h4>
<div class=\"container\">
";
while ($row = mysql_fetch_row($get)) {
echo "

<div class=\"name\">$row[1]</div><br /> \n

<div class=\"email\">$row[2]</div><br /> \n

<div class=\"subject\">$row[3]</div><br /> \n

<div class=\"comments\">$row[4]</div><br /> \n

";
}
echo "</div>";
?>
[/syntax:802aaa9e47]

[Jamie]

Bingo! Your a star Alex, thanks ever so much. Over the last couple of days you, Dave (sonicgroup) and Marble have helped a great deal and I learnt have a massive amount about PHP. The script is far from finished but, for the time being, I'll give you a rest. Thanks a lot once again.

[Jamie]

Alex, how would I go about changing the order of output so that new entries go above old entries rather than below?

I have looked on php.net but can't find anything.

[sonicgroup]

Well normally, a table would have a date field of some kind that you can sort on, but in your case, you don't so you can sort by the ID (higher IDs would be more recent).

Change your query to this:


SELECT * FROM entries ORDER BY id DESC

That will still get all the entries, but it will order them by the ID with the highest ID first and descending from there.

[Jamie]

I see, thanks Dave. :)

[absolethe]

Oh my god! I hate that! I vastly prefer mysql_fetch_array! It allows you to use your field names. I tend to even go one step further. My typical query looks like this:

[syntax:4427b81462="php"]
<?php
$getblog = mysql_query("SELECT * FROM blog_entries ORDER BY bl_date DESC") or die($uhoh=mysql_error());

while($entry = mysql_fetch_array($getblog)){

$bl_id = $entry['bl_id'];
$bl_title = $entry['bl_title'];
$bl_date = $entry['bl_date'];
$bl_text = $entry['bl_text'];
$bl_by = $entry['bl_by'];
$bl_music = $entry['bl_music'];
$bl_mood = $entry['bl_mood'];
?>

<div class="blog_entry">
<div class="blog_head">
<?php echo("$bl_title"); ?>
<?php echo("$bl_date"); ?>
</div>
<div class="blog_text">
<?php echo("$bl_text"); ?>
<br/>
...<?php echo("$bl_by"); ?>
</div>
<div class="blog_foot">
<?php echo("$bl_mood"); ?>
<?php echo("$bl_music"); ?>
<a href="blog_comments.php?id=<?php echo("$bl_id"); ?>" title="Comment">Comment</a>
</div>
</div>

<?php
}
?>
[/syntax:4427b81462]

I think the above is vastly easier to read. However, going from $entry['bl_id'] to $bl_id is highly optional.

[sonicgroup]

I hate that! ;) Seriously, the best way is to use mysql_fetch_object. It allows for much cleaner and more readable code in my opinion. Also, I'd strongly suggest against assigning your query values to variables - in my opinion it's a waste of time, space, and processing power.

mysql_fetch_object allows you to use OOP formatting to access your query variables:

[syntax:67e49fe3e2="php"]
while ($line = mysql_fetch_object($result)) {
echo '<p>'.$line->bl_title.'</p><p>'.$line->bl_date.'</p>';
}
[/syntax:67e49fe3e2]

and so on and so forth.

[absolethe]

But it's also a waste of processing power to use "echo" for your looped results, I've been told. And OOP and concantation look like a huge mess to me. Maybe if I used them regularly they wouldn't.

[sonicgroup]

echo actually uses the least amount of processing power to print text (so I'm not sure where you're getting that from). Granted, outputting directly from the loop is not the best way to do it. I usually assign any data to an array and then have a separate output function to put it all together.

At any rate, the OOP is cleaner in my opinion because it avoids using brackets and extra quotes. This is especially helpful to me when using them in this manner (i.e. concatenating the data with an HTML string).

[absolethe]

I've read that in above situation, doing what I did...closing out the php and then typing in the html...is the best way to output your results. That echo (when you're echoing the value of variables) processes the HTML as well, etc. I've read it on several forums and in at least 1 tutorial.

I still really like fetch_array...I got into the habit of assigning to variables because the first tutorial I read whose method worked for me did that. Plus, I like the way it looks. But when I think about it, I guess the computer ends up doing everything twice...

I've only recently started learning PHP, so I guess I can stop doing that. I only learned the word concantation two weeks ago--before that I couldn't figure out why all these people kept sticking all these dots in their echos.

I guess the combination of '<p>'.$blahblah.'</p>' is what avoids that problem. The concantation omits the need for double quotes... I wonder why people don't clearly explain these things in tutorials? That is the whole point of the tutorial, isn't it?

I still like the look of

<p>
<?php echo("$entry[$bl_title"); ?>
</p>

better. It more clearly separates the two. Even someone who didn't know PHP could edit the HTML so long as they didn't screw with with anything between <?php and ?>.

[sonicgroup]

Yea, there was a discussion about single vs. double quotes on here a while back if I'm not mistaken.

Basically it breaks down like this: A string enclosed in single quotes is treated just like a string by the PHP interpreter. So, this:

[syntax:98761f587a="php"]
$foo = 'bar';
echo 'This is a variable: $foo.';
[/syntax:98761f587a]

is printed to the screen exactly as it looks: This is a variable: $foo.

However, double quotes are a different matter. PHP will parse strings in double quotes for variables before it does whatever with the string.

So the example above, in double quotes, would be printed as: This is a variable: bar, because PHP parses the string for variable replacements.

The nice thing about using single quotes is that you don't have to worry about escaping double quotes for HTML attributes when you use them in a string in PHP. It also forces you to concatenate everything rather than using direct variable insertion. This has 2 benefits: 1) It's faster because the PHP interpreter doesn't have to parse the whole string, and 2) It's easier to see where your variables are (if you use an editor with syntax highlighting) because they are not inside a string and are colored correctly as PHP variables, rather than part of the string.

You can still concatenate with double quotes, but it defeats the purpose. One other tip, if you are using double quotes: surround the variable with braces - this makes sure the value is replaced and it provides a nice delimiter for easy location of the variables for you:

[syntax:98761f587a="php"]
$foo = 'bar';
echo "This is a variable: {$foo}";
[/syntax:98761f587a]

Also, you should drop the parentheses from your echo statements - they're not necessary, and in my opinion, clutter everything.

I've read that in above situation, doing what I did...closing out the php and then typing in the html...is the best way to output your results. That echo (when you're echoing the value of variables) processes the HTML as well, etc. I've read it on several forums and in at least 1 tutorial.

As for that: If you enclose the HTML in single quotes, it doesn't get parsed. But you are correct: process then display is the best way to do it. Normally I would not echo directly from a loop like that. As I said, I would assign the data to a variable (array) and then output it at a later stage.

[absolethe]

I don't totally understand what you mean there. You wouldn't use the loop to at all? I'm kinda thinking here, "Isn't it already in an array after you've 'fetched' it?" If you have a chance, can you show me an example? :) I have gotten a little used to the way I'm doing everything, but I'm always happy to try a better way...so long as it doesn't look too crazy and make me dizzy.

[sonicgroup]

Sure. As you may know, I wrote the Snaps! Gallery (http://labs.sonicdesign.us/projects/Snaps!/) image gallery.

Here's one of the functions that I use in it:

[syntax:09815ca6a4="php"]
/**
* Function: album() - lists images in an album
*
* @access public
* @var integer $albumID - ID of album to display
*/
function album($albumID) {
global $db, $title, $config, $start;
/* Get the album name */
$result =& $db->query('SELECT * FROM '.TP.'albums WHERE albumID = '.$albumID);
if (DB::isError($result)) {
die($result->getMessage());
}
$line =& $result->fetchRow(DB_FETCHMODE_ASSOC);
$albumTitle = stripslashes($line['albumName']);
$result =& $db->query('SELECT * FROM '.TP.'images WHERE albumID = '.$albumID);
if (DB::isError($result)) {
die($result->getMessage());
}
$numImages = $result->numRows();
/* Get the images in the album */
$result =& $db->query('SELECT * FROM '.TP.'images WHERE albumID = '.$albumID.' LIMIT '.$start.','.$config['imagesPP']);
if (DB::isError($result)) {
die($result->getMessage());
}
$albumImages = $result->numRows();
/* If we have images, get their information */
if ($albumImages > 0) {
$i = 1;
while ($line =& $result->fetchRow(DB_FETCHMODE_ASSOC)) {
$image[$i][0] = '?album='.$albumID.'&image='.$line['imageID'];
$image[$i][1] = stripslashes($line['imageName']);
if ($config['enableCache'] == 1) {
$image[$i][2] = getImage($line['albumID'], $line['imageFilename'], $line['imageName'], 100, 'cache');
} else {
$image[$i][2] = getImage($line['albumID'], $line['imageFilename'], $line['imageName'], 100, 'dynamic');
}
$image[$i][3] = stripslashes($line['imageDesc']);
$image[$i][4] = $line['imageCreated'];
$image[$i][5] = $line['imageModified'];
$image[$i][6] = $line['imageViews'];
if ($config['allowComment'] == 1) {
/* Get number of comments for each image */
$rslt =& $db->query('SELECT COUNT(*) FROM '.TP.'comments WHERE imageID = '.$line['imageID']);
if (DB::isError($rslt)) {
die($rslt->getMessage());
}
$ln =& $rslt->fetchRow(DB_FETCHMODE_ASSOC);
$image[$i][7] = ($ln['COUNT(*)'] > 1) ? $ln['COUNT(*)'].' comments' : (($ln['COUNT(*)'] == 1) ? $ln['COUNT(*)'].' comment' : 'No comments');
}
$i++;
}
$image[1][9] = crumb('index', $albumTitle, '');
if ($numImages > $config['imagesPP']) {
$image[1][8] = makePagination('album', $albumID);
} else {
$image[1][8] = 'Page 1 of 1';
}
/* otherwise, we have no images */
} else {
$image = 'There are no images in this album yet!';
}
return $image;
}
[/syntax:09815ca6a4]

This is a little more complicated example than what we're talking about here, but it's the same principle. The difference here is that I'm using a multi-dimensional array ($image) to store the data (the various pieces of information about each image). I assign all that to the array, and then the function returns the array. The piece of code that called the function then takes this array and does its magic to display it.

My point about not using the array to display has to do with where you need to display information. Rather than trying to keep the entire resultset in memory (and a connection open), I typically get all the information I need into another array I can use. This frees the resultset memory and closes the connection to the database, which allows for better performance. Also, if you have values from a query that you need to display half a page apart, it's easier to have the data available in that array outside the loop, rather than the resultset array where you'd be in a loop and echoing (a lot of HTML in my example).

[absolethe]

Far more modular... Kind of exciting. If I could wrap my head around this, it would be terribly useful considering how I have my tables set up... I really get confused when I look at classes and complicated functions. But maybe if I stare at it long enough.

Thanks. I'm gonna go look at this.

[sonicgroup]

Download the whole gallery from the site I posted. Take a look at the index.php in there - it's what calls all the functions. Then you can see how it all falls together. That should help you understand what's going on there. Also, feel free to add me to any messengers you have (mine are in profile) if you have questions or want to discuss it. ;)

[sonicgroup]

Also, back on the echo/print thing, take a look at this (http://dynacker.dotgeek.org/printvsecho/). echo is 5% faster than print.

[Marble]

print returns a value of 1 or 0 and echo doesn't - thus the difference in speed.

[sonicgroup]

True, but how often are you going to need to verify that sending something to the buffer actually worked? Probably never. ;)

[Marble]

Yep.. its better to use echo and its faster... you can also append to the same variable and echo the page content in one lot rather than bits thru the site...

[absolethe]

Sonic,

Until I find the time/energy to develop a more modular function-based structure for my site, would the following be any better than my previous method, or simply essentially the same?


$contentinfo = mysql_query("SELECT pg_name,sec_ref,content_display,code_in,code_href, html_in,html_href FROM pages WHERE pg_name='$_GET[p]' AND sec_ref='$_GET[s]'");
$countinfo = mysql_num_rows($contentinfo);
while($cresult = mysql_fetch_array($contentinfo)){
$pg_name =& $cresult["pg_name"];
$sec_ref =& $cresult["sec_ref"];
$content_display =& $cresult["content_display"];
$code_in =& $cresult["code_in"];
$code_href =& $cresult["code_href"];
$html_in =& $cresult["html_in"];
$html_href =& $cresult["html_href"];
}

?

[sonicgroup]

It's essentially the same. You're still assigning the results to individual variables. Not doing that saves memory (as each variable requires some to hold it), and improves the speed by not having to do erroneous assignations.

[absolethe]

The book I just read claimed that the reference variable and original variable referred to the same value. I felt that this meant I wouldn't have

$variable1 = bunches of stuff
$variable2 = bunches of stuff

But something more along the lines of

$variable1 & $variable2 = bunches of stuff

Which would have some benefit to memory.

[sonicgroup]

There is some benefit (over straight assignment) because the reference only acts as a pointer to the stored value, but you still need memory for the pointer, and you still have erroneous variables.

[absolethe]

I figured that would be the case. I was looking at is as no more than a fast, temporary way to relieve at least some of the problem.

Thanks for your help, by the way. I always thought functions would be neat, but couldn't get a good grasp on how I could use them for what I was doing. Until you showed me how to return an array from a database query, it had never occured to me that it fell under the umbrella of a result I could return from a function. I've already changed a few of smaller queries (using a sort-of catch-all version), I just want to develop functions for my more complicated chunks (that use if statements and such).

[sonicgroup]

Yea, returning data to a display function is a much cleaner way to deal with data than trying to display it as you come across it.