[RESOLVED] Sometimes It Should Just Be Left To The Experts

hdsprint

Okay, I'm a noob, and I've tried to figure this PHP stuff out and it just blows my mind.

What I want to do is create a searchable database for firearms.

I borrowed a sample database code off the Net and managed to get it connected to my GoDaddy MySQL database just fine (I stopped getting errors concerning connections to the database so I believe I got it connected just fine.

Now I get the following:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/content/a/l/l/alleydude/html/firearms/page1.php on line 70

I've seen this in other threads with replies that seem to work, but I just don't know enough to apply what was suggested to get mine to work.

The code I am using is just a template that I was hoping to alter to apply to what I want to do, but I think it is all over my head.

Here is the code to my page:

<!--very good data entry page,with confirmation page!//-->
<html>
<head>
<title>data entry page</title>
</head>
<body bgcolor="CCCCCC">
<?php  

//header include for data entry page. [not nessesacery you can delete this line #10]
$db = mysql_connect("host", "user", "pass");
// might have to change user name and host name.
mysql_select_db("test",$db); 
// data base name 

//i have used my test data vase here.
//you will have to create a table called employees in your test DB in mysql.
//with the following mysql query:
//mysql>use test;
//database changed
//mysql>create table employees( id tinyint(4) default '0' not null auto_increment,
// ->first varchar (255),
// ->last varchar(255),
// ->address varchar(255),
// ->position mediumtext,
// ->primary key (id),
// ->unique id (id));
//
//then go back and view this page and start your data base,
//you will need to create a new php page to veiw the info in your site!
//to upload images from this form simply type in where you will find the image on you server eg:<img src="C:/images/image.gif"> 
//and this is alot faster that uploading a image to the data base 

if ($submit) {

// here if no ID then adding else we're editing

if ($id) {

$sql = "UPDATE employees SET first='$first',last='$last',address='$address',position='$position' WHERE id=$id";

} else {

$sql = "INSERT INTO employees (first,last,address,position) VALUES ('$first','$last','$address','$position')";

}

// run SQL against the DB

$result = mysql_query($sql);

echo "<img src=button1.gif><br><p>Record updated/edited!</p><a href=$PHP_SELF>[ Refresh ]</a>";

} elseif ($delete) {

// delete a record

$sql = "DELETE FROM employees WHERE id=$id"; 

$result = mysql_query($sql);

echo "<img src=button1.gif><br><p>$sql Record deleted!</p><a href=$PHP_SELF>[ Refresh ]</a>"; } else {

// this part happens if we don't press submit

if (!$id) {

// print the list if there is not editing

$result = mysql_query("SELECT * FROM employees",$db);

while ($myrow = mysql_fetch_array($result,MYSQL_NUM)) {

printf("<li><a href=\"%s?id=%s\">%s    </a>\n", $PHP_SELF, $myrow["id"], $myrow["first"]);

printf("   <a href=\"%s?id=%s&delete=yes\"><b>[Delete]</b></a><br><br>", $PHP_SELF, $myrow["id"]);

}

}

?>

<P>

<a href="<?php echo $PHP_SELF?>">[Add a record]</a>

<P>

<form method="post" action="<?php echo $PHP_SELF?>">

<?php



if ($id) {

// editing so select a record

$sql = "SELECT * FROM employees WHERE id=$id";

$result = mysql_query($sql);

$myrow = mysql_fetch_array($result);

$id = $myrow["id"];

$first = $myrow["first"];

$last = $myrow["last"];

$address = $myrow["address"];

$position = $myrow["position"];

// print the id for editing

?>
<input type=hidden name="id" value="<?php echo $id ?>">
<?php
}
?>
<table border="1" cellspacing="1" cellpadding="7"><tr><td>
Page title!</td><td><input type="Text" name="first" value="<?php echo $first ?>"><br>
</td></tr>
<tr><td>
Header or Images.</td><td><input type="Text" name="last" value="<?php echo $last ?>"><br>
</td></tr>
<tr><td>
Info.</td><td><input type="Text" name="address" value="<?php echo $address ?>"><br>
</td></tr>
<tr><td valign="top">
HTML content!</td><td>
<textarea type="input" name="position" value="" rows="20" cols="70"><?php echo $position ?></textarea>
</td></tr>
<tr><td valign="top">
<input type="Submit" name="submit" value="Enter information"></td><td valign="top">
<input type="reset" name="reset" value="clear fields"><br>
</form>
</td></tr>
</table>
<?php
}
?>
</body>
</html>

I appologize if this is this is not the correct way to post this. I am really lost. I can do html all day long, but PHP has me lost.

Any help would be appreciated.

bradgrafelman

You might want to start from scratch... that script has a few things wrong with it. :p

For one, it depends on register_globals to be enabled, a directive that has long ago been declared a security exploit and even removed from PHP6. Instead, incoming data should be accessed using one of the superglobal arrays; see this manual page for more information: [man]variables.external[/man].

Also, $PHP_SELF isn't ever defined either; instead, look at using the $_SERVER superglobal array. More information on these supergobal arrays can be found here: [man]variables.predefined[/man].

Also, user-supplied data should never be placed directly into a SQL query. Instead, it should first be sanitized with a function such as [man]mysql_real_escape_string/man (or, in the case of integers, you can simply cast them to an (int) instead).

Finally, that code contains some invalid HTML markup as well. There's also some SQL optimizations that could be made, but I think you've got enough to start fixing as it is. :p

hdsprint

Wow.

Okay, I read your reply several times, and you may as well have been talking Greek to me.

Then I click on the links you pointed me to and I'll be danged if they are in Russian or something. :eek:

I thought I felt stupid BEFORE I came here. Now I REALLY feel dumb. 😃

So what's up with the Russian text on the links you pointed me to? I'll check my usersettings, maybe it's something on my end.

Thanks for the help, even though I understood almost nothing of what you suggested.

:queasy:

hdsprint

Okay, got the Russian thing (Bulgarian, actually) worked out. D'OH!

shawty

I think what bradgrafelman was trying to say is that the code you choose to try and modify for learning purposes was probably one of the worst examples you could have picked. :-)

As for your error message, that generally occurs when there are no results returned from the SQL statement used to query your database, and where you then go on to try and use the result with out first checking to see if there is anything there.

A really good example of a simple query to retrieve listing from the database can be found in the PHP manual at http://www.php.net/ in the MySQL database section.

You'll likely find however that the error you have is caused by your select statement being wrong. Look for a line in your code somwhere that looks like this :

" SELECT abc,def,ghi FROM table WHERE jkl = '123' "

What thats saying is return any data in the fields called "abc" "def" and "ghi" from the table called " table " in my database, but ONLY if the data in field "jkl" is equal to the value "123"

If there are NO records in the database where a field called "jkl" has the value "123" then NO records will be returned, and thus when you try to fetch a row using mysql_fetchrow the PHP interpreter aborts with the error your getting.

Ideally what you need to do is check your result using the mysql_num_rows function before you attempt to read the rows from the database.

If the num_rows function returns 0 then simply skip trying to get the data and close the database connection.

The MySQL page on the PHP site can be found at :

http://www.php.net/manual/en/book.mysql.php

I strongly encourage you to play with the "MySQL extension overview example" before you do anything else, look at the description of each of the functions used in the rest of the manual, and read the user contributed notes, there is a wealth of information in there.

Forget anything more complicated until you've mastered retrieving rows of data, because MOST MySql operations can be based on that template with very little modification.

Cheers

Shawty

hdsprint

Thank you for the help. I will try to build a simple, one field database of say, "Names" and see if I can get it to list them for me.

Is the first part of this code okay to start with?

<?php
$db = mysql_connect("host", "user", "pass");
mysql_select_db("test",$db);

This, I believe connects to my MySQL database and I believe this works.

Thanks again!

shawty

Absolutley correct...

🙂

mysql_connect connects to the database at Internet address "host" using username "user" and password "pass"

mysql_select_db selects the database called "test" from the server you opened with mysql_connect.

Your next step is to construct a query similar to what i described above, then execute it using mysql_query.

Remember to save the result you get from the call to mysql_query, then use mysql_num_rows to check if you have any results, and finally mysql_fetch_row to retrieve the data.

Your learning already.... keep it up....

🆒

hdsprint

Wow. I appreciate the help and encouragement, but this is really blowing my mind. It's like trying to figure out an internal combustion engine without first understanding fire.

I see the strings to use and the definitions of what they do, but applying them is just way over my head.

I think if I could see an example of a functioning code that connects to a database, allows for insertion of data, and a means to retrieve it, I would be much farther ahead.

The database I want to create has about 4-5 fields. I want to be able to go to a page to enter data, another page to display the data. I'd also like to be able to search the database and display the applicable entries.

I am believing that I may have to hire someone to do this for me, or is it just so simple that I can't see it?

Again, thanks for the help and input.

shawty

The magic is all in the SQL statements. All your inserts, all your selects all your updates, the PHP code is almost identical in each case, it's the SQL statements that do the magic.

When you interact with MySQL using PHP the code sequence is always:

mysql_connect
mysql_select_db
mysql_query
mysql_close

or in the case of a select

mysql_connect
mysql_select_db
mysql_query
loop:
mysql_fetch_row
endloop:
mysql_close

As for your actual SQL statements

SELECT * FROM <table> : will select all fields from all rows in table <table>

INSERT INTO <table> VALUES('value1','value2','value3','value4') : will insert value1,value2,value3 & value4 into fields 1,2,3,4 of table <table>

DELETE FROM <table> WHERE field = 'value' : Deletes a row from table <table> where a column called 'field' contains the value 'value'

As i mentioned in my previous post the PHP MySQL Manual is at

http://www.php.net/manual/en/book.mysql.php

Look at the page "MySQL extension overview example" where you will find an extremely simple & easy to follow MySQL PHP example, and indeed the one that most of us cut our teeth on.

The path to learning this stuff does often seem hard, and at first seems like you'll never get anywhere, but believe me, even the best of us where there once, and look where we are now 🙂

Stick at it, even when your darkest hour seems like it'll never brighten, you will find light at the end of the tunnel, i promise you.

Cheers

Shawty

hdsprint

Okay, as my English teacher used to say, "Learning has taken place."

I basically copied the code from the example above that looks like this:

<?php
// Connecting, selecting database
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
    or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
mysql_select_db('my_database') or die('Could not select database');

// Performing SQL query
$query = 'SELECT * FROM my_table';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// Printing results in HTML
echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo "\t<tr>\n";
    foreach ($line as $col_value) {
        echo "\t\t<td>$col_value</td>\n";
    }
    echo "\t</tr>\n";
}
echo "</table>\n";

// Free resultset
mysql_free_result($result);

// Closing connection
mysql_close($link);
?>

I inserted my database information and with a little trial and error I managed to get the following result:

Connected successfully

No errors, nothing. Okay, with this baby step behind me, I need to get to a point where I can enter data.

I have set up in MySQL a database with three fields (for now):

manufacturer
model
price

By the way... You guys ROCK!

shawty

Fantastic 😃

Now, to get to the serious bit....

See the line that looks like the following:

$query = 'SELECT * FROM my_table';

this is your actual SQL statement. Now if you look at my example statement's above you can see the INSERT one, your first step is to replace the old SQL with this new one.

You have manufacturer, model & price in your database so in SQL speak you need to INSERT INTO those fields.

If these are the ONLY 3 columns in your database, then you can get away with doing the following:

"INSERT INTO <table> values('$manufacturer','$model','$price')"

Remember to replace <table> with the name of your database table.

If there are other fields in your database, then you'll need to explicitly tell MySQL exactly which fields to insert the data into, something like the following :

"INSERT INTO <table> (manufacturer,model,price) values('$manufacturer','$model','$price')"

Once you've constructed the correct SQL syntax, then it's just a simple matter of calling mysql_query(<query here>) and checking the result using mysql_affected_rows()

This will return an integer telling you how many operations where completed. If you where inserting just one row, then this integer should be 1.

Unlike the SELECT SQL in the example your playing with, you can get rid of the loop and the mysql_fetch row statements. If mysql_affected_rows() returns a positive result, then you can skip directly to closing the database.

Cheers

Shawty

hdsprint

Okay, I believe I did what you suggested and I get the error:

Parse error: syntax error, unexpected T_STRING in /home/content/a/l/l/alleydude/html/firearms/page2.php on line 12

<?php
// Connecting, selecting database
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password') 
    or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
mysql_select_db('firearmspg') or die('Could not select database');

// Performing SQL query

INSERT INTO <my_table> values('$manufacturer','$model','$price')
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// Printing results in HTML
echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo "\t<tr>\n";
    foreach ($line as $col_value) {
        echo "\t\t<td>$col_value</td>\n";
    }
    echo "\t</tr>\n";
}
echo "</table>\n";

// Free resultset
mysql_free_result($result);

// Closing connection
mysql_close($link);
?>

And what's with the "or die" thing. That's pretty harsh for messing up a little code... 😃

shawty

LOL... 🆒

Easy mistake to make...

Look at your previous file :

// Performing SQL query
$query = 'SELECT * FROM my_table';

now look at your new file :

// Performing SQL query
INSERT INTO <my_table> values('$manufacturer','$model','$price')

What do you notice is different, apart from the actual SQL query?

first thing is that your not assigning the SQL to a variable.

$query = "INSERT INTO <my_table> values('$manufacturer','$model','$price')"

secondly, you need to change <my_table> to the name of your table, so this then becomes :

$query = "INSERT INTO my_table values('$manufacturer','$model','$price')"

or if the table in your database is called gun_list then something like :

$query = "INSERT INTO gun_list values('$manufacturer','$model','$price')"

The actual error message that PHP give you, essentially means that PHP encountered a string when it didn't expect one, in the way you added the SQL to your code, this will have been caused by the first ' just before $manufacturer

Remember also to get rid of the

// Printing results in HTML
echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo "\t<tr>\n";
    foreach ($line as $col_value) {
        echo "\t\t<td>$col_value</td>\n";
    }
    echo "\t</tr>\n";
}
echo "</table>\n";

// Free resultset
mysql_free_result($result);

bit of code, as thats only needed if your SELECTING and displaying values. for inserts it's not required.

shawty

The die() function is used to premeturely abort the script when an error occurs, typicly you put an error message in the brackets eg:

die("OOPS the script went bang")

you wouldn't normally use die() in a production environment, but it comes in really handy when testing.

Any function that outputs a string can be used in a die() statement and is often used as follows when working with mysql:

... or die ("error : " . mysql_error())

So that the script aborts and displays the reason the mysql function failed.

hdsprint

Okay, I made the changes (see below) but I still get this error. My table is called my_table.

Parse error: syntax error, unexpected T_VARIABLE in /home/content/a/l/l/alleydude/html/firearms/page2.php on line 14

Here is the code so far:

<?php
// Connecting, selecting database

<?php 
// Connecting, selecting database 
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password') 
    or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
mysql_select_db('firearmspg') or die('Could not select database');

// Performing SQL query

$query = "INSERT INTO my_table values('$manufacturer','$model','$price')"
$result = mysql_query($query) or die('Query failed: ' . mysql_error());


// Closing connection
mysql_close($link);

?>

I do appreciate the help.

hdsprint

Could it be because $manufacturer, $model, and $price have no value at this point?

shawty

Ah.... now that one, gets even the best of us sometimes, and to be honest i really don't know why it occurs (If anyone else has the answer I'd love to know), i do however know how to resolve it.

You need to split up the variable parts in the values section of the SQL statement. so changing it from :

values('$manufacturer','$model','$price')

values('" . $manufacturer . "','" . $model . "','" . $price . "')

should do the trick.

One way i like to construct insert statements is by using an array, if you haven't learned about arrays just yet, then you may want to look in the main articles on PHP Builder, as i'm currently writing a series called the 'ABC of PHP' and i'll be covering arrays in part 7.

anyway on with the code :


$values = array();
$values[0] = $manufacturer;
$values[1] = $model;
$values[2] = $price;

$queryvalues = join("','",$values);
$query = "INSERT INTO my_table values('" . $queryvalues . "')";

I find using the above method is much cleaner, and less error prone beacuse you don't have to type lots of ' and " marks to join everything.

shawty

hdsprint;10907030 wrote:
Could it be because $manufacturer, $model, and $price have no value at this point?

Not at all, as the variables are not defined then all that will happen is the SQL will just insert empty/blank values into the database.

For testing though, you should assign some test data using :

$manufacturer = "man name";
$model = "mod name";
$price = "price";

hdsprint

Okay, setting the Arrays aside for now, here is what I have:

<?php
// Connecting, selecting database

$manufacturer = "man name";
$model = "mod name";
$price = "price";

$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password') 

or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
mysql_select_db('firearmspg') or die('Could not select database');

// Performing SQL query

$query = "INSERT INTO my_table values("'.$manufacturer.'","'.$model.'","'.$price.'")"
$result = mysql_query($query) or die('Query failed: ' . mysql_error());


// Closing connection
mysql_close($link);

?>

And this gives me THIS error now:

Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in /home/content/a/l/l/alleydude/html/firearms/page2.php on line 17

I certainly applaud you patience. :o

shawty

You have the " and ' symbols the wrong way round...

" '.$manufacturer.' "

should be

' " . $manufacturer . " '

The error message was reporting that you have your string enclosures incorrectly specified, and while PHP knew you where trying to define string variables, it couldn't quite work out how they where supposed to go together.

😃

You also need to make the same changes round $model & $price