Weborum Webmaster Forum > mySQL: Connecting to your DB info

Just Another Artist

May 16 2006, 06:58 PM

Hi,

Joe2kiss has been helping me out with DB's and below are some quotes of his code that I have questions about.

First of all, I'm using WampServer (PHP5, mySQL5) on my computer for testing purposes.
For the password, I have set it to nothing for now, but will set it once I upload it to the net.

CODE

$dbHost = 'localhost';
$dbUser = 'root';
$dbPass = '';
$dbName = 'sermons';

$db = mysql_connect("$dbHost","$dbUser","$dbPass");
if (!$db) { die('Error : ' . mysql_error()); }

$select_db = mysql_select_db($dbName,$db);
if (!$select_db) { die('Error : ' . mysql_error()); }

And here is the query code that I've *sort of* edited for my needs.

CODE

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>Query test page</title>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
<meta http-equiv="content-language" content="en-us" />
<meta http-equiv="Imagetoolbar" content="no" />
<link rel="stylesheet" href="sah-css.css" type="text/css" />

</head>
<body>
<?php include_once ("global_config.php"); ?>

// All HTML code here, until you want to run your query

<?php
$sql = "SELECT UserID, Username, EmailAddr, EncPass, EmailSent, Joined FROM User WHERE Verified = '0'";
$result = mysql_query($sql, $db);
$count = mysql_num_rows($result);
if($count == 0)
{
    echo '<p>No unvalidated members.</p>';
}
while($row = mysql_fetch_assoc($result))
    {
    $UserID = $row['UserID'];
    $Username = stripslashes($row['Username']);
    $EmailAddr = stripslashes($row['EmailAddr']);
    $EncPass = stripslashes($row['EncPass']);
    $EmailSent = stripslashes($row['EmailSent']);
    $Joined = date("d.m.y",$row['Joined']);

    echo '<div class="Member">'."\n";
    echo '    <h4>'.$UserID.' - <a href="mailto:'.$EmailAddr.'" title="Send mail to '.$Username.'">'.$Username.'</a> <span class="Validation"><a href="http://www.joe2torials.com/reg/check.php?U='.$Username.'&E='.$EmailAddr.'&S='.$EmailSent.'&EP='.$EncPass.'" title="User Validation Link">(click to validate)</a></span></h4>'."\n";
    echo '    <p><em>Registered: not yet registered</em></p>'."\n";
    echo '</div>'."\n\n";
    }
?>

</body>
</html>

I said all of that to ask this: I don't understand the first line of variable values (is that the correct term??).

QUOTE

$sql = "SELECT UserID, Username, EmailAddr, EncPass, EmailSent, Joined FROM User WHERE Verified = '0'";

Can I simply use: SELECT * FROM sermonaudio ? (Side question: CAPS isn't an issue in mySQL is it?)

Basically what I am trying to do here is this:
I have a table called sermonaudio that has multiple fields (AudioDate, AudioTitle, AudioSpeaker). From a particular <td></td> tag set, I want to call the AudioDate, and from another <td>, AudioTitle. [I'm getting ahead of myself aren't I?]

Can you give some direction to this newbie please?

Thanks,
Just another artist

Joe

May 17 2006, 03:32 PM

Hi Shad,

First things first, let's work on your SQL. I can't remember what your table looks like so you may need to refresh my memory. What you want to do is pull only the information from the database you intend to use.

SQL

SELECT * FROM table_name

This query will pull everything from the table. Whereas, if your not actually going to be using everything you only need to pull the fields that you will use;

SQL

SELECT AudioID, AudioName, AudioSpeaker, AudioDate FROM table_name

Rather than pulling everything you will now only pull information from those rows.

No, CAPS isn't an issue for MySQL itself, but the field names are case-sensitive.

Now for the PHP.

[php]<?php include_once ("global_config.php");

$sql = "SELECT AudioID, AudioName, AudioDate FROM Sermons";
$result = mysql_query($sql, $db);
$count = mysql_num_rows($result);
if($count == 0)
{
echo '<p>No records available.</p>';
}
while($row = mysql_fetch_assoc($result))
{
$AudioID = $row['AudioID'];
$AudioName = stripslashes($row['AudioName']);
$AudioDate = date("d.m.y",$row['AudioDate']);

// Echo out everything here.

}
?>[/php]

As you can see, the query is grabbing the data from those specific fields. Then the variables are extracting the information from each field.

Hope this helps clarify a few points.

Just Another Artist

May 17 2006, 09:38 PM

I see what you're saying @ selecting specifics.

Okay, I've updated my code and ran a test and this time no errors (or anything for that matter) displayed, so I think I'm on the right track. Now, what is a bit perplexing to me is the fact that I do have db field values entered and yet they didn't show up. Reference the screen shot of my SQL window.

Thanks for the help,
Shad

Just Another Artist

May 17 2006, 10:06 PM

Quick ammendment:
How do you edit posts??

Also, Since I will be uploading audiofiles from other days, AudioDate might be a useless field as the date is not going to reflect the actual date of when the audio file was created. So should I simply enter the date with HTML?

Shad

Joe

May 18 2006, 07:06 AM

What code did you just use?

With HTML? Just use an input box to type the date in manually.

Members are blocked from editing their posts after a certain amount of time ... I think.

Just Another Artist

May 18 2006, 04:08 PM

CODE

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>Query test page</title>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
<meta http-equiv="content-language" content="en-us" />
<meta http-equiv="Imagetoolbar" content="no" />
<link rel="stylesheet" href="sah-css.css" type="text/css" />

</head>
<body>
<?php include_once ("config.php");

$sql = "SELECT AudioID, AudioTitle, AudioDate FROM sermonaudio";
$result = mysql_query($sql, $db);
$count = mysql_num_rows($result);
if($count == 0)
{
echo '<p>No records available.</p>';
}
while($row = mysql_fetch_assoc($result))
{
$AudioID = $row['AudioID'];
$AudioTitle = stripslashes($row['AudioTitle']);
$AudioDate = date("d.m.y",$row['AudioDate']);

// Echo out everything here.

}
?>

</body>
</html>

Joe

May 18 2006, 05:03 PM

You realise that you actually have to echo out your information for it to be seen don't you?

[php]// Echo out everything here.[/php]

http://us2.php.net/manual/en/function.echo.php

Just Another Artist

May 18 2006, 05:06 PM

ahahahaha oops! *blushes*

Just Another Artist

May 18 2006, 05:46 PM

Yay!!

I'm making visable progress!!

CODE

<?php include_once ("config.php");

$sql = "SELECT AudioID, AudioTitle, AudioDate FROM sermonaudio";
$result = mysql_query($sql, $db);
$count = mysql_num_rows($result);
if($count == 0)
{
echo '<p>No records available.</p>';
}
while($row = mysql_fetch_assoc($result))
{
$AudioID = $row['AudioID'];
$AudioTitle = stripslashes($row['AudioTitle']);
$AudioDate = date("d.m.y",$row['AudioDate']);

// Echo out everything here.
echo "Title:" .$AudioTitle."<br>";
}
?>

Now, just to play around with the out formatting here a bit and see what other things I can get to work.

Shad

Just Another Artist

May 18 2006, 06:49 PM

Okay, here is my progress in displaying the db info with HTML. But how do I keep it from creating a new table for each db field entry? Also, is my PHP coding ethics viable? Last, but not least, can you briefly explain the $row function, because I looked it up on php.net and didn't really get it. *looking for another explaination*

CODE

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>Query test page</title>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
<meta http-equiv="content-language" content="en-us" />
<meta http-equiv="Imagetoolbar" content="no" />
<link rel="stylesheet" href="sah-css.css" type="text/css" />

</head>
<body>
<?php include_once ("config.php");

$sql = "SELECT AudioID, AudioTitle, AudioDate FROM sermonaudio";
$result = mysql_query($sql, $db);
$count = mysql_num_rows($result);
if($count == 0)
{
echo '<p>No records available.</p>';
}
while($row = mysql_fetch_assoc($result))
{
$AudioID = $row['AudioID'];
$AudioTitle = stripslashes($row['AudioTitle']);
$AudioDate = date("d.m.y",$row['AudioDate']);

?>

<table cellpadding="5" cellspacing="0" border="1">
<tr>
<th><?php echo "Title:" ?></th>
</tr>
<tr>
<td><?php echo $AudioTitle; ?></td>
</tr>
</table>
<?php
}
?>
</body>
</html>

Thanks,
Shad

Joe

May 18 2006, 07:31 PM

The while statement is an iteration. For each record within the database it will output whatever you place within the curly brackets { } - so;

[php]while($row = mysql_fetch_assoc($result)) {
echo $AudioID;
}[/php]

If you ran the above, on your page you would get (assuming you have 5 records in your database);

CODE

12345

As you can see, they are all on the same line. If you want to make these multi-line you can then add your HTML;

[php]while($row = mysql_fetch_assoc($result)) {
echo '<p>$AudioID</p>';
}[/php]

Which would output;

CODE

1
2
3
4
5

On screen. So what if you want to add more HTML formatting? Easy.

If you want all of your output to appear within a table the first thing you should do is open your table before any PHP has run, so;

CODE

<table properties etc>
<?php ....

Then within your loop you can add each record as a table row;

[php]while($row = mysql_fetch_assoc($result)) {
echo '<tr>';
echo '<td><p>$AudioID</p></td>';
echo '</tr>';
}[/php]

Which would give you your basic layout of;

CODE

--------------
|      1     |
--------------
|      2     |
--------------
|      3     |
--------------
|      4     |
--------------
|      5     |
--------------

And then at the end of your PHP you can close the table tag.

Just Another Artist

May 18 2006, 07:47 PM

I see @ how to code php inside of tables

..i think. here's what my code looks like now, but it doesn't recognize $AudioTitle in the td section.

CODE

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>Query test page</title>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
<meta http-equiv="content-language" content="en-us" />
<meta http-equiv="Imagetoolbar" content="no" />
<link rel="stylesheet" href="sah-css.css" type="text/css" />

</head>
<body>

<table cellpadding="5" cellspacing="0" border="1">
<?php include_once ("config.php");

$sql = "SELECT AudioID, AudioTitle, AudioDate FROM sermonaudio";
$result = mysql_query($sql, $db);
$count = mysql_num_rows($result);
if($count == 0)
{
echo '<p>No records available.</p>';
}
while($row = mysql_fetch_assoc($result))
{
echo '<tr>';
echo '<th>Title</th>';
echo '</tr>';
echo '<tr>';
echo '<td>$AudioTitle</td>';
echo '</tr>';
}
?>
</table>
</body>
</html>

Just Another Artist

May 19 2006, 01:58 AM

I would keep testing alternate methods to the code in the previous post if I knew any, but being so new to this world, I simply have to wait for help. But I do test methods on my own if I can. Guess I'm saying all of that to say that I'm not merely relying on people to do my "dirty work" for me.

Shad

Joe

May 19 2006, 03:53 PM

That's because you aren't grabbing anything out of the database;

[php]$AudioID = $row['AudioID'];
$AudioTitle = stripslashes($row['AudioTitle']);
$AudioDate = date("d.m.y",$row['AudioDate']);[/php]

The $row variable extracts the returned information from the database query and then you can extract each fieldname from that like the above.

Just Another Artist

May 19 2006, 06:31 PM

I re-added the $row vars, but it's still not working for me. Is there a typo?

CODE

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>Query test page</title>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
<meta http-equiv="content-language" content="en-us" />
<meta http-equiv="Imagetoolbar" content="no" />
<link rel="stylesheet" href="sah-css.css" type="text/css" />

</head>
<body>

<table cellpadding="5" cellspacing="0" border="1">
<?php include_once ("config.php");

$sql = "SELECT AudioID, AudioTitle, AudioDate FROM sermonaudio";
$result = mysql_query($sql, $db);
$count = mysql_num_rows($result);
if($count == 0)
{
echo '<p>No records available.</p>';
}
while($row = mysql_fetch_assoc($result))
{
$AudioID = $row['AudioID'];
$AudioTitle = stripslashes($row['AudioTitle']);
$AudioDate = date("d.m.y",$row['AudioDate']);

echo '<tr>';
echo '<th>Title</th>';
echo '</tr>';
echo '<tr>';
echo '<td>$AudioTitle</td>';
echo '</tr>';
}
?>
</table>
</body>
</html>

*Looking up some infor on $row*

Just Another Artist

May 19 2006, 07:35 PM

Here's a screenshot of what's happening with the code from the code I just posted.

Joe

May 19 2006, 07:58 PM

It is because you are only echoing text. Say if $varname contained the word 'Joe', then;

[php]echo '$varname';[/php]

Will output;

CODE

$varname

While;

[php]echo $varname;[/php]

Will output;

CODE

Joe

Although when you add more HTML into the echo you will have;

[php]echo '<p>$varname</p>';[/php]

Which will output;

CODE

$varname

This is because it isn't reading the PHP correctly, as far as the echo is concerned you are echoing basic text. What you need to do is;

[php]echo '<p>'.$varname.'</p>';[/php]

Which will then give you;

CODE

Joe

Any clearer?

Also, $row isn't a pre-defined variable, it can be anything, if you want to find out what it is, you need to look at the function contained within it - mysql_fetch_assoc;

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

Just Another Artist

May 19 2006, 08:03 PM

Okay, I see the difference between the two is the quotes, but now I'm receiving an error. (sorry for sounding so dumb)

error message:
Parse error: parse error, unexpected T_STRING, expecting ',' or ';' in H:\wamp\www\rcc\test-query.php on line 32

CODE

<table cellpadding="5" cellspacing="0" border="1">
<?php include_once ("config.php");

$sql = "SELECT AudioID, AudioTitle, AudioDate FROM sermonaudio";
$result = mysql_query($sql, $db);
$count = mysql_num_rows($result);
if($count == 0)
{
echo '<p>No records available.</p>';
}
while($row = mysql_fetch_assoc($result))
{
$AudioID = $row['AudioID'];
$AudioTitle = stripslashes($row['AudioTitle']);
$AudioDate = date("d.m.y",$row['AudioDate']);

echo '<tr>';
echo '<th>'Title'</th>'; [b]// this is line 32[/b]
echo '</tr>';
echo '<tr>';
echo '<td>'$AudioTitle'</td>';
echo '</tr>';
}
?>
</table>

Oh Wait! I forgot the concat's.
*Checking out that link*

Just Another Artist

May 19 2006, 08:16 PM

wahoo! Got it~! I forget the concatenation for the vars.

CODE

<table cellpadding="5" cellspacing="0" border="1">
<?php include_once ("config.php");

$sql = "SELECT AudioID, AudioTitle, AudioDate FROM sermonaudio";
$result = mysql_query($sql, $db);
$count = mysql_num_rows($result);
if($count == 0)
{
echo '<p>No records available.</p>';
}
while($row = mysql_fetch_assoc($result))
{
$AudioID = $row['AudioID'];
$AudioTitle = stripslashes($row['AudioTitle']);
$AudioDate = date("d.m.y",$row['AudioDate']);

echo '<tr>';
echo '<th>Title</th>';
echo '</tr>';
echo '<tr>';
echo '<td>'.$AudioTitle.'</td>';
echo '</tr>';
}
?>
</table>

THANKS!

Shad