In Which AHunter3, FileMaker geek, learns MySQL and PHP

Miscellaneous and Personal Stuff I Must Share
1

I’d like to thank the folks who responded to me in my helpme thread. ZipperJJ suggested that I do a blog on the process, but I’m a ways off from being able to bang out a PHP+MySQL-powered website so for now this is my blog. I will probably ask some questions as well as report how I’m doing…

I’ve enrolled with NYU SCPS for two courses (general SQL for the first one, MySQL + PHP for the second one) that will take place this winter & spring. I’m working ahead in the mean time but it will do my soul some good to be in a classroom again, I think.

On my own, with the aid of the book that Lobsang recommended to me (Vaswani’s How to do Everything with PHP & MySQL), I’ve gotten this far:
a) Created a four-table relational database in MySQL from the command line, based on a FileMaker 2.1 database that was my first professional-calibre FileMaker database. I printed out the field defs from FileMaker and created data fields of the same names (minus spaces and illicit characters) in MySQL.

b) Downloaded the drivers that let me reference the running MySQL db from FileMaker itself; converted the original FmPro 2.1 db to modern FmPro format (.fp7) and duplicated the main layout and switched the table reference to MySQL; created a temporary relationship between orig fmpro tables and corresponding MySQL tables and used FmPro scripts to move data into MySQL tables.

c) Now that I have data as well as structure in MySQL, practiced doing SELECT queries from the command line. select client.firstname, client.lastname, reoptxfr.casetype, reoptxfr.datefield, reoptxfr.closedon from client, reoptxfr where client.assignedworkerslastname = “hunter, allan” and client.statusofcase = “1 Active Case” and client.clientid = reoptxfr.clientid order by client.lastname, client.firstname, reoptxfr.sequencenumber;

Cool beans :smiley:
Looks ugly as hell. I’ve got to get cracking on the PHP. Here’s where I’m at with the PHP:
I’ve got it installed; I’ve created a form that lets me enter something and it returns the results in a specified filename.php file which contains the php code that in turn “does things” with what was sent to it. I’ve read ahead on the syntax of If / End If and declaring variables and it’s a lot like FileMaker (how nice!) and a bit like AppleScript (cool!) so I think I’ll be OK with that. But I don’t have PHP on speaking terms with the MySQL database yet. That’s today’s project: get PHP to tell MySQL to return it something no matter how primitive.

2

My first PHP designed to chat up the SQL:

[noparse]
<html>
<head></head>
<body>

<?php

$connection = mysql_connect(‘localhost’, ‘root’, ‘’)
or die (‘Unable to connect!’);

mysql_select_db (‘ebcoa’) or die (‘Unable to select database!’);

$query = “select client.firstname, client.lastname, reoptxfr.casetype, reoptxfr.datefield, reoptxfr.closedon from client, reoptxfr where client.assignedworkerslastname = “hunter, allan” and client.statusofcase = “1 Active Case” and client.clientid = reoptxfr.clientid order by client.lastname, client.firstname, reoptxfr.sequencenumber”;

$result = mysql_query ($query)
or die ('Error in query: $query. ’ . mysql_error());
if (mysql_num_rows ($result) >0)

echo ‘<table width=100% cellpadding=10 cellspacing=0 border=1>’;
echo
‘<tr><td><b>FIRSTNAME</b></td><td><b>LASTNAME</b></td><td><b>CASETYPE</b></td><td><b>OPENED</b></td><td><b>CLOSED</b></td></tr>’;
while ($row = mysql_fetch_row($result))
{
echo ‘<tr>’;
echo ‘<td>’ . $row[0] . ‘</td>’;
echo ‘<td>’ . $row[1] . ‘</td>’;
echo ‘<td>’ . $row[2] . ‘</td>’;
echo ‘<td>’ . $row[3] . ‘</td>’;
echo ‘<td>’ . $row[4] . ‘</td>’;
echo ‘</tr>’;
}
echo ‘</table>’;
}
else
{
echo ‘No rows found!’;
}

mysql_free_result ($result);

mysql_close($connection);
?>

</body>
</html>
[/noparse]
The not so successful results.

Hmmph. ::goes back to staring at code for typos & stuff::

3

Instead of doing this:



while ($row = mysql_fetch_row($result))
{
echo '<tr>';
echo '<td>' . $row[0] . '</td>';
echo '<td>' . $row[1] . '</td>';
echo '<td>' . $row[2] . '</td>';
echo '<td>' . $row[3] . '</td>';
echo '<td>' . $row[4] . '</td>';
echo '</tr>';
}

You can do this:



while ($row = mysql_fetch_row($result))
{
echo '<tr>';
    foreach ( $row as $field ) { 
        echo '<td>' . $field . '</td>';
    }
echo '</tr>';
}
4

Doing the same thing here but attacking from a different angle. I started with just a simple database of four fields (key, fname, lname, note) and then started tackling PHP. Figure I can hone the database later. I’d rather concentrate on security than database structure.

5

When I’ve got a little free time, I’m interested in this combo, too. We’ve got a couple of free apps running on our website that use them, but I haven’t really dived into the code yet.

6

Nice! Doesn’t fix the problem though:

Well OK technically when I run it the way you typed it out (less klunky code version), the top line above the ‘table’ section looks like this:

  1. echo ‘’; echo ‘’; while ($row = mysql_fetch_row($result)) { echo ‘’; foreach ( $row as $field ) { echo ‘’; } echo ‘’; } sw echo ’

But the underlying problem ain’t fixed at any rate.

7

Addendum: I found a missing }, located right after

if (mysql_num_rows ($result) >0)

Oddly, adding it in, saving file, & reloading it in the browse didn’t make a bit of difference :confused:

8

You need to escape your quotes in your query:


$query = "select client.firstname, client.lastname, reoptxfr.casetype, reoptxfr.datefield, reoptxfr.closedon from client, reoptxfr where client.assignedworkerslastname = \"hunter, allan\" and client.statusofcase = \"1 Active Case\" and client.clientid = reoptxfr.clientid order by client.lastname, client.firstname, reoptxfr.sequencenumber";
9

Are you using an editor of any sorts? Something like UltraEdit will make stuff like missing curly braces and unescaped strings jump right out at you.

Don’t think you’re doing yourself any favors by hashing everything out in Notepad. You’re not :slight_smile:

10

BBEdit, baby. and yes it highlights non-matching pairs.

ptr2void: bless you. That makes all kinds of sense and I never would have seen it.

11

Hmm, I have now escaped by search criteria as ptr2void noted that I should. I am entirely sure that doing so is necessary. It is not, unfortunately, sufficient. My results look pretty much the same. Something else is still fouling it up.

12

Can you post up your current code? What are the errors you are getting presently?

13

You’re not supposed to use " to denote string in SQL. It’s single quote.

SELECT * FROM table WHERE field = ‘string’

You do not have to escape those when building your query as a string, either.

14

Is your page actually being executed?

I recently had an issue while setting up a couple of Bugzilla instances on a Linux box where one worked, but the other would show source code instead of the rendered output.
It was a simple case of forgetting to configure Apache to allow CGI scripts to run in the second directory tree.

I don’t know jack about PHP, but you might want to do a silly sanity check to make sure your code is actually being chewed on and processed, rather than being dumped directly to the screen (and the screenshot appears to be the browser interpreting the raw unexecuted code).

15

The code is upthread right on these very pages (post #2), with a few minor fixes that didn’t succeed in fixing anything. The errors I am currently getting are show in the screen shot also referenced at the end of post #2.

I have also tried a much simpler PHP code that for the select statement just does this:

$query = ‘select * from client’;
Same result.

I’ve tried with " and with ’ and with escaping them and with not escaping them; and as I said above also with avoiding the whole issue by simply querying $query = ‘select * from client’;.
minor7flat5, can you say what you said as if you were speaking to a much more ignorant person? Specifically: how the heck would I be able to tell?

Hmm OK let me backtrack, this is probably relevant: Exercise One was this little html page:[noparse]

<html>
<head></head>
<body>
<form action=“test.php” method=“post”>
Enter your message: <input type=“text” name=“msg” size=“30”>
<input type=“submit” value=“Send”>
</form>
</body>
</html>[/noparse]

The reply I got back on clicking the button was indeed test.php and test.php included code that said, in essence “you just posted this: ____” trapping for what had been send along to test.php.

So I assume that means Apache and PHP are on speaking terms with each other, is that what you were asking?

16

OK, I believe I figured it out, thanks to minor7flat5, and understanding that your “errors” were exactly the same as before.

First, heed ZipperJJ’s advice regarding single quotes in SQL queries. I should’ve pointed you in that direction. Alas, I’ve been away from MySQL for too long.

Second, the screenshot seems to indicate that the page to which you’re going has an html extension, which falls in line with m[sup]7[/sup]♭[sup]5[/sup]'s suggestion; that is, the PHP is not being parsed and interpreted, so it’s just being output to the screen as is. The key to this is the first line printed, which if you compare to the code you’ll notice follows the > symbol in the


while (mysql_num_rows($result) > 0) {

line. This means that the browser is interpreting everything between the opening


<?php

and that > symbol as an html tag, and sort of just ignoring it.

17

OK.

Changed file extensions on all variants of file to .php

Made REAL simple file with just the following code:[noparse]
<html>
<head></head>
<body>
<?php
$connection = mysql_connect(‘localhost’, ‘guest’, ‘pass’)
echo ‘hello world’;
mysql_close($connection);
?>
</body>
</html>
[/noparse]

Result: totally blank white page.

Comment out the $connection = mysql_connect line and the mysql_close($connection); line and try again and I get: “hello world”
Does this indicate that PHP is simply not speaking to MySQL? The latter is definitely up & running. I have it open in a Terminal window where I can do queries & whatnot.

Oh!: other variants tried (the "guest’ / ‘pass’ is straight out of the book):

$connection = mysql_connect(‘localhost’, ‘root’, ‘’) <—— I never set a root pw but I know root is enabled; dunno if I have to DO SOMETHING to enable guest access?! So I thought I’d try this. No difference.

$connection = mysql_connect(‘localhost’, ‘ahunter3’, ‘mypassword’) <—— I granted all on * to ahunter3 identified by mypassword. No difference here either.

18 

Made REAL simple file with just the following code:
<html>
<head></head>
<body>
<?php
// You were missing the semi-colon after this line:
$connection = mysql_connect('localhost', 'guest', 'pass'); // <-- here
echo 'hello world';
mysql_close($connection);
?>
</body>
</html>
19

I would have to dissent most vociferously with that last statement. It’s not even made compellingly clear that this is true of Mac OS X. The whole books kind of treats OS X as an afterthought. At any rate there are no Step One, Step Two type instructions for total newbs like me on how to install these freaking missing MySQL libraries in an OS X environment.

I’m ordering more books.

20

This code works! [noparse]
<html>
<head></head>
<body>

<?php

$connection = mysql_connect(‘localhost’, ‘guest’, ‘pass’);
mysql_select_db (‘ebcoa’);

$query = ‘select client.firstname, client.lastname, reoptxfr.casetype, reoptxfr.datefield, reoptxfr.closedon from client, reoptxfr WHERE client.clientid = reoptxfr.clientid order by client.lastname, client.firstname, reoptxfr.sequencenumber’;

$result = mysql_query ($query);
if (mysql_num_rows ($result) >0)
{
echo ‘<table width=100% cellpadding=10 cellspacing=0 border=1>’;
echo
‘<tr><td><b>FIRSTNAME</b></td><td><b>LASTNAME</b></td><td><b>CASETYPE</b></td><td><b>OPENED</b></td><td><b>CLOSED</b></td></tr>’;
while($row = mysql_fetch_row($result))
{
echo ‘<tr>’;
echo ‘<td>’ . $row[0] . ‘</td>’;
echo ‘<td>’ . $row[1] . ‘</td>’;
echo ‘<td>’ . $row[2] . ‘</td>’;
echo ‘<td>’ . $row[3] . ‘</td>’;
echo ‘<td>’ . $row[4] . ‘</td>’;
echo ‘</tr>’;
}
echo ‘</table>’;
}
else
{
echo ‘No rows found!’;
}

mysql_free_result ($result);
echo ‘hello world’;
mysql_close($connection);
?>

</body>
</html>
[/noparse]
OK I am still having problems when I try to specify that I only want records returned where client.assignedworkerslastname = “hunter, allan”. Have tried the “hunter, allan” part in double quotes not escaped, like that; have tried in single quotes not escaped; have tried in double quotes and escaped; in single quotes and escaped. Nada.

But it’s definitely a start! :slight_smile: