PHP Newby Simple PHP Help to Pull mySQL

[frankb3]

I'm a volunteer host of my high school's alumni association website. HTML pages for every class year, back to 1957. I just recently setup all class listing in a mySQL database connected locally to a Filemaker 11 front end.
I'm reading a couple books on PHP and am trying find relevant examples on how to pull the data and echo it to each class page, and update the home page with the latest listings.
Basic field structure including a photo, data is being parsed from WuFoo forms.

Can someone help point me in a good direction as far as PHP is concerned. The samples I'm finding seem to be based on a web form of some sort - but nothing so far that fits with what I'm trying to do. I'm deploying it a little differently so that I can still preview and manage the posts before hitting the site and keep my local database up to date.

Thanks,

Frank

[rajivatre]

Hi Frank,
I can help. PM me what exactly you want and I can give you whatever solution such as sample code etc.
Since you are working as a volunteer, there will not be charge from my side whatsoever.

Thanks
Rajiv

[frankb3]

Rajivatre,

Thanks. I'm wanting to pull fields from mySQL into a three column table on each html page, based on class year, and whether an alumni has passed away. Those are handled in a separate table on each class page. I also need to post the most recent updates on the main page in a column by class year, and then at the bottom, those who have passed away under a heading called "remembrance".

I will also need to apply my website CSS for the text styles and color.

The calculated fields are done locally in Filemaker and synced to mcSQL, so it's an easy task to pull data the way I want without too much trouble.

Any suggestions?

Thanks,

Frank

[rocknbil]

Welcome aboard! So you've exported the Filemaker data to mySQL? Excellent. :-)

You don't want to "create pages" for each member, you want the pages to output dynamically from your PHP script. Basically pull the record(s) from mySQL with PHP, insert them in a template, and output to the browser.

First you need to get your mySQL to a web site. You'd do a mysql dump of your database which will give you a file with an .sql extension. (unless FileMaker has already created an .sql file for you.)

Now you go into your web site control panel and create the database, then locate/log in to phpMyAdmin. In phpMyAdmin, use the import function to import your data.

Now comes the steep learning curve. :-) You need to learn PHP to connect with the database and output the data. Here's a good start [w3schools.com]. If you've never done PHP, start from the top, but you will get to the "meat" of the project in the Database section.

You will also need the manual [php.net] and will frequently need to require the mySQL manual [dev.mysql.com].

A small hint: don't do in programming what can be done in mySQL statements - before answering "how would I do this?" first scour the mySQL manual to see if there's a way to do it in mySQL statements. A good example is the robust time and date functions in mySQL.

[frankb3]

rocknbil,

Thanks for the info. I've been reading a lot of these materials already, these have been added to the library! I found a pretty simple example that I'm trying on my table that partially works. However, when I introduce a "WHERE" command, the result is a mySQL error. Here is the PHP I'm using. Any pointers?

mysql_select_db("my_db", $con);

$result = mysql_query("SELECT * FROM my table ORDER BY Deceased DESC WHERE Class Year='1977'");

echo "<table border='1'>
<tr>
<th>Class Year</th>
<th>First Name</th>
<th>Last Name</th>
</tr>";

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['Class Year'] . "</td>";
echo "<td>" . $row['First Name'] . "</td>";
echo "<td>" . $row['Last Name'] . "</td>";
echo "</tr>";
}
echo "</table>";

mysql_close($con);
?>

Removing the "WHERE" will successfully echo my entire table. So I'm a little stumped.

Here is the error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

[jspeed]

Try backticking the field name. It may not like that there is a space.

WHERE `Class Year`='1977'

[jspeed]

Actually, your WHERE may be in the wrong spot after looking at it:

$result = mysql_query("SELECT * FROM `my table` WHERE `Class Year`='1977' ORDER BY `Deceased` DESC");

[frankb3]

jspeed

You are correct. The switch in order fixed it. How do I add an additional WHERE condition?

[jspeed]

SELECT * FROM `my table` WHERE `Class Year`='1977' OR `Class Year`='1978' ORDER BY `Deceased` DESC

[rocknbil]

Right, the reason why you need to backtick is the space in the table name, which is generally not a good idea but the backticks fix it.

A W.O.W. using or: form the habit of parenthesizing or's. look at this:

$query = "SELECT * FROM `my table` WHERE some_table_value=1 and `Class Year`='1977' OR `Class Year`='1978' ORDER BY `Deceased` DESC";

So if some_table_value=1 and class year = 1977, it will return true, which may be expected.

But . . . if **only** the class year is 1978, it will still return true even if some_table_value is **not** 1, when you expect it to bind it to some_table_value=1. "this and that (false) . . . or that (true) . . . "

Parenthesizing the or binds either of the "or" conditions to the "and".

$query = "SELECT * FROM `my table` WHERE some_table_value=1 and (`Class Year`='1977' OR `Class Year`='1978') ORDER BY `Deceased` DESC";

[frankb3]

Now that I have it working, the CONCAT function seems to be the best method to string a couple of fields together and return it into one table column. However, being new to the Syntax, I'm not sure where that would be placed in my code. I would also like to place a carriage return between the two and code first field with STRONG text.
Any suggestions...or pointers. The multiple examples make sense, just not comfortable with putting everything together. I'm such a newbie! Thanks for your patience.