🔒 Locked 🤖 Godot

[web] PHP & MySQL

Started by Sshado Oct 26, 2004 at 10:21 AM 11 replies 2.7k views

Original Post

Oct 26, 2004 at 10:21 AM

I am creating a form that people would enter their website address in and it would save to a database. I then want to display the results, how do I convert the data into a hyperlink? this didn't work:



$sql = "INSERT INTO projects SET 
          projectname='$projectname', 
          projectstartdate=CURDATE(),
	  projectwebsite=''$projectwebsite''";

mainly, this line didn't work:


	  projectwebsite=''$projectwebsite''";

I can get it to work when I display the results, but it is a lot of work, I was hoping to get it to convert when it enters with the above code.

Instruo

Oct 26, 2004 at 10:28 AM

without more information, I'd have to take a guess that your slashes are screwing you. In SQL (like in a lot of formats), the "\" character is the is escape character identifier, so if it just seems one of them its thinking it needs to escape the next character. What you'll need to make sure of is that you double up on all your slashes.

Now, this may or may not be the problem, but it's a pretty common thing. If you can't get it sorted out, post back here with a copy of your full query (i.e. echo it instead of sending it to the database) and we'll have a look.

Sshado

Oct 26, 2004 at 10:32 AM

I updated it to show source, didn't realize how it looked without being source :p

Rob Loach

Oct 26, 2004 at 10:49 AM

You have to retreive the data from the database....

$result = Query(  "SELECT * "  . " FROM `projects` "  . " WHERE 1");// Go through and output the postswhile($row = GetNextRow($result)) {    $projectname = $row['projectname'];    $projectswebsite = $row['projectwebsite'];    echo "$projectname
";}

Oh, and I frequently use my own database.php.

Never have pure HTML inside a database. Remember, it's good to keep your content and design seperate. Make the HTML when you get the data. I haven't tried the above source, so be warned.

This line:

projectwebsite=' a href='$projectwebsite' target='_new'>'$projectwebsite' /a>'";

Should be just:

projectwebsite='$projectwebsite'";

Instruo

Oct 26, 2004 at 10:54 AM

Okay, here goes:

First, from a design standpoint, it's probably better to only store $projectwebsite instead of the entire link. You can very easily recreate the link when you go to display the data, it leaves it more flexible and it takes up less storage (thus, less time to transfer the data back to the client).

Secondly, you seem to be combining the syntax of INSERT and UPDATE. Correct syntax for both are:

INSERT INTO tablename (attribute, attribute) VALUES (value, value);UPDATE tablename SET attribute='value', attribute='value';

On the technical end, it looks like your problem lies in your 's. The ' is what SQL uses to identify the beginning and ending of a string in a query. So, what MySQL is seeing in your query there is:

"INSERT INTO projects SET           projectname='$projectname',           projectstartdate=CURDATE(),	  projectwebsite='<a href='

And then it blows up, because it thinks that second ' is the end of the value you want to put in to the projectwebsite attribute. So, since it thinks that, it blows up when it gets to your value of $projectwebsite, because it's not a valid SQL command.

To include ' and \ as part of a value stored in a database, we use the escape character "\" to preceed them. So, when inserting these things, you'll need to put a \ in front of characters that SQL interprets as commands, to get it to ignore that character as a command. Example:

INSERT INTO business (name) VALUES ('Jim\'s Carwash');

Okay, how to fix this:
First, save yourself a lot of trouble and go with the design I mentioned above. That'll cut out a ton of the problems in this particular piece and is just better practice ;) Then, change the way your formatting your INSERT statement to match the example. Then, you'll want to make sure that every ' and \ are preceeded by a \ (check out php.net for search functions, pretty easy to do with PHP).

Add all this together and hopefully it'll be sorted out :) Write back if you have any more questions. Good luck!

Sshado

Oct 26, 2004 at 11:30 AM

OK, I think I understand it all now. I just have one question now:

How do I convert the following code to print each field instead of all the fields:

// Printing results in HTMLecho "<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";}

The code works and prints everything, but if I were to change the one line:

echo "\t\t<td>$col_value</td>\n";

to:

echo "\t\t<td>$col_value</td>\n";

This makes all of the colums a hyperlink. I guess what I am asking is, how do I print each column individually?

I know this should be easy enough.

Sshado

Oct 26, 2004 at 11:35 AM

Here is the full code for showing the results if it helps:

<?php// Connecting, selecting database$link = mysql_connect('host', 'username', 'password')   or die('Could not connect: ' . mysql_error());mysql_select_db('database') or die('Could not select database');// Performing SQL query$query = 'SELECT projectname, projectwebsite FROM projects';$result = mysql_query($query) or die('Query failed: ' . mysql_error());// Printing results in HTMLecho "<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 resultsetmysql_free_result($result);// Closing connectionmysql_close($link);?>

Sshado

Oct 26, 2004 at 12:27 PM

Thanks for all your help, I was able to figure it out after looking at webmonkey.com

GroZZleR

Oct 29, 2004 at 10:48 PM

Quote:
Original post by Rob Loach
Oh, and I frequently use my own database.php.

There's a small bug in your database class.

function Close(){    mysql_close();}

By not including the $link (stored from Connect()), you're closing the last connection to be opened.

$DatabaseOne = new Database();$DatabaseTwo = new Database();$DatabaseOne->Connect("blah", "blah", "blah", "db1");$DatabaseTwo->Connect("blah", "blah", "blah", "db2");$DatabaseOne->Close();$DatabaseTwo->Query("SELECT .... ");

The query would fail, because $DatabaseOne->Close() just closed $DatabaseTwo's connection.

Probably never used two databases (I know I haven't), so never saw the bug come up. Just a word to the wise - decent class otherwise. =)

Rob Loach

Oct 31, 2004 at 12:52 PM

Thanks Grozzler. Yeah, I made it for only handling one database at a time as that's all I ever use. I'm currently redesigning the backend of my website focusing primarily on easy manipulation of content and system expansion. With this, I was thinking of making a SQL extension that would assist with making the SQL statements. If I do this, it would turn out something like this:

$database = Database($host, $username, $password, $db);$database->Select("*", $table, $optionalWhereStatements);$database->Insert($table, $statements);

... Or something like that. I'm still working on the design. I plan on releasing the whole backend system when complete. It's coming along quite nicely so far. You can log in and edit content very easily. Also runs off a css theming system. I should base it off of the CSS Garden. That would be pretty crazy.

A standard page looks like php.net's pages:

<?phpinclude 'prepend.php';site_header($title, $menu);?><h1>What's up?!</h1>Not much!
<?phpsite_footer();?>

Through the admin system you can edit any php file directly from the file. Still working on it. If you have any comments about the SQL class, just tell me.

Sander

Oct 31, 2004 at 1:20 PM

There's a really neat trick in the new phpBB2.2 database class (see phpBB's area 51) that you could use very well Rob. It's a function that builds insert and update queries from arrays to save you a lot of manual typing. Something like this:

$my_array = array(  'id' => 1,  'foo' => 'bar');$insert_sql = $db->make_insert($my_array, 'my_table');//returns "INSERT INTO my_table (id, foo) VALUES ('1', 'bar')"$where = 'val=1';$update_sql = $db->make_update($my_array, 'my_table', $where);//returns "UPDATE my_table SET id=1, foo='bar' WHERE val=1"

It's really usefull when inserting/updating tables with many fields.

Rob Loach

Nov 1, 2004 at 3:26 PM

Quote:
Original post by Sander
There's a really neat trick in the new phpBB2.2 database class (see phpBB's area 51) that you could use very well Rob. It's a function that builds insert and update queries from arrays to save you a lot of manual typing. Something like this:

*** Source Snippet Removed ***

It's really usefull when inserting/updating tables with many fields.

That's pretty cool. I can't find the 2.2 snapshot though and I can't find the function declared in the one currently provided. Ah well, I'll work on something else in the mean time.

Sander

Nov 2, 2004 at 4:42 AM

The development version of 2.2 is numbered 2.1.x so you can get your snapshot here (2.1.2).

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.