You are not logged in.

#26 20 Mar 2007 8:42 am

MadHatter
Administrator
From: Dallas TX
Registered: Jun 2006
Posts: 529
Website

Re: Advanced PHP Includes

Code:

$query = "UPDATE news SET title='$title', category='$category', content1='$content1', author='$author', date='$date', ip='$ip', WHERE ID=$id";

the important part is that the syntax is: set column_name=value, column_name=value, not set value=value, value=value, because value is not the name of the column, and mysql will not know where to insert what.  also, wrap the string values in quotes.

Offline

 

#27 20 Mar 2007 11:22 am

Butcher
Moderator
From: Norway
Registered: Jul 2006
Posts: 308

Re: Advanced PHP Includes

Ahh, then I clearly misunderstood that part, so, the first title in title='$title' specifies the column? I tought it was a system that would tell the database to call the old title, then change it to the new title, as set in the row $title.

So, it seems that the error I've had is that I've double echoed it, it should be like your query up there says, as of where I would have the dollar sign before what is supposed to be the column name as well as having it in the updated content.

I will test it as soon as the FTP will allow me to connect again, damn this internet.


http://bamboocommandos.com/butcher_img/butchersig7.jpg

Offline

 

#28 20 Mar 2007 12:18 pm

Butcher
Moderator
From: Norway
Registered: Jul 2006
Posts: 308

Re: Advanced PHP Includes

Hmm, I tested it now, and got this echo:

Code:

UPDATE news SET title='', category='Other News', content1='Testing once more, with no batman stuff!', author='bananas and cookies', date='20th of March, 2007', ip='83.109.235.176', WHERE ID=7

The syntax, being colum='$data' should work, since that is what I am using now. The string is now wrapped in the ' symbols, like I just wrote in bold. Though, the database is not updated. Could it be because I am simply having it query the ID, then bringing it like this into the process form:

Code:

$id= mysql_real_escape_string($_POST['id']);

And then running this command to the query:

Code:

WHERE ID=$id

The column name for the table is not capital letters, but lowercase "id", can that cause the problem? Or is it something about the method used for choosing the correct line, WHERE ID=$id, is not correct?


http://bamboocommandos.com/butcher_img/butchersig7.jpg

Offline

 

#29 20 Mar 2007 12:20 pm

MadHatter
Administrator
From: Dallas TX
Registered: Jun 2006
Posts: 529
Website

Re: Advanced PHP Includes

Butcher :

Ahh, then I clearly misunderstood that part, so, the first title in title='$title' specifies the column?

yea, it sets the value of that column to what $title is.

Butcher :

So, it seems that the error I've had is that I've double echoed it, it should be like your query up there says, as of where I would have the dollar sign before what is supposed to be the column name as well as having it in the updated content.

yea, your table has columns:

Code:

id | title | category| author | content1 | date
---------------------------------------------------
3  | foo   |  bar    |  me    |          |

so the update statement tell what columns need to be updated, and the new values to add.

Code:

update my_table set title="new foo", category="new bar"

will set every row's title column to "new foo", and every row's category column to "new bar". 

adding the WHERE clause, allows you to specify which row you want to change. 

it would be really difficult and inaccurate if the database just matched and replaced random column values based on values given in a query (you can sort of do this, but the syntax is quite a bit different), seeing as how its not uncommon for databases to hold gigabytes of data, you have to tell it what column you wish to update, and what the value it needs to update with, as well as what row you want this to affect.

Offline

 

#30 20 Mar 2007 12:31 pm

MadHatter
Administrator
From: Dallas TX
Registered: Jun 2006
Posts: 529
Website

Re: Advanced PHP Includes

Butcher :

Hmm, I tested it now, and got this echo:

Code:

UPDATE news SET title='', category='Other News', content1='Testing once more, with no batman stuff!', author='bananas and cookies', date='20th of March, 2007', ip='83.109.235.176', WHERE ID=7

The syntax, being colum='$data' should work, since that is what I am using now. The string is now wrapped in the ' symbols, like I just wrote in bold. Though, the database is not updated. Could it be because I am simply having it query the ID, then bringing it like this into the process form:

Code:

$id= mysql_real_escape_string($_POST['id']);

And then running this command to the query:

Code:

WHERE ID=$id

The column name for the table is not capital letters, but lowercase "id", can that cause the problem? Or is it something about the method used for choosing the correct line, WHERE ID=$id, is not correct?

what I'd really recomend for you, since this is all pretty much new to you, is to download XAMPP (it installs apache web sever, with PHP, and MySql, and an FTP server too if you wish) and mysql tools, this way, you can create the database on your own computer, and use the MySql Query Browser, to send the query directly to the database.  what this will do for you is allow you to see what works and what doesnt, and when it doesnt, mysql gives you an error (usually saying read the manual for XXX or YYY).  then once you get the query down, all you do is copy it into your PHP file, and replace the actual values you have in the query browser, for the PHP variables used to do the updating / inserting or querying ...

I typically develop on my own machine, then push the final finished product (PHP and database scripts) to the site when I have it all working.


as for your question

Butcher :

The column name for the table is not capital letters, but lowercase "id", can that cause the problem?

the answer is, "somtimes"  on windows machines the table names are case insensitive, but on *nix machines they are case sensitive.  the formal way you should reference the column name (and table name too for that matter) is wrapping it in back ticks (on my keyboard, its the character on the same key as the tilde ~) so:

Code:

update `table` set `title`='some title', `category`='category value' where `id`=5

some times its hard to tell the difference between the back tick and the single quote.  database names, table names, and column names should be put inside back ticks (Microsoft's SQLServer uses brackets [database].[table].[column] like mysql uses back ticks: `database`.`table`.`column`). and you should make sure the casing matches how you have it set up on the server.

Offline

 

#31 20 Mar 2007 1:02 pm

Butcher
Moderator
From: Norway
Registered: Jul 2006
Posts: 308

Re: Advanced PHP Includes

Yes, I used XAMP for some time, to test it. I will reinstall big_smile

But, as far as it seems, it is now down to smaller details like what type of sign I use where, and if it is lower case or capital letters. The basic syntax I am using, should work, shouldnt it?


http://bamboocommandos.com/butcher_img/butchersig7.jpg

Offline

 

#32 20 Mar 2007 1:32 pm

MadHatter
Administrator
From: Dallas TX
Registered: Jun 2006
Posts: 529
Website

Re: Advanced PHP Includes

yea

Offline

 

#33 29 Mar 2007 12:01 pm

Butcher
Moderator
From: Norway
Registered: Jul 2006
Posts: 308

Re: Advanced PHP Includes

Hmmm, I've tested a lot with XAMP now, and it still does not work correctly. As far as I've understood, the syntax I am using should work, it goes like this:

I open the file "edit_news.php", and it successfully queries the database:

Code:

<?php
mysql_select_db ("danwew_news");
$result = mysql_query("SELECT * FROM news ORDER BY ID DESC LIMIT 4");

while($row = mysql_fetch_array($result))
  {
$row['id'];
echo "&nbsp;";
echo "<a href=\"select_news_edit.php?ID=$row[id]\">$row[title]</a>";
echo "<br />";
  }
?>

I click the link, which takes me to the file "select_edit_news.php", and again successfully queries the database:

Code:

<?php
mysql_select_db("danwew_news");
$sql = "SELECT * FROM news WHERE ID = '".mysql_escape_string($_GET['ID'])."'";
$result = mysql_query($sql);

while($row = mysql_fetch_assoc($result))
  {
  echo $row['id'];
  }
?>

Then, I press the submit button and it uses the "POST" function to send the information to the file "process_edit_news.php", which is where I think the problem is;

Code:

<? 
mysql_select_db("danwew_news") or die(mysql_error()); 
$id= mysql_real_escape_string($_POST['id']); 
$title = mysql_real_escape_string($_POST['title']); 
$category = mysql_real_escape_string($_POST['category']); 
$content1 = mysql_real_escape_string($_POST['content1']); 
$author = mysql_real_escape_string($_POST['author']); 
$date = mysql_real_escape_string($_POST['date']); 
$ip = mysql_real_escape_string($_POST['ip']); 

$query = "UPDATE news SET title='$title', category='$category', content1='$content1', author='$author', date='$date', ip='$ip', WHERE id='$id'";
echo $query;
?>

The syntax is correct according to all sites and tutorials I can find, it is put up with "column name='$new content'", and the column names correspond with the database (case sensitivity as well). And it is obvious that the $query should update the specified columns with the new content from the previous page, and that the function WHERE id='$id' should choose the row in the database that I just edited the content for. But why, oh why, wont it work?!


http://bamboocommandos.com/butcher_img/butchersig7.jpg

Offline

 

#34 29 Mar 2007 1:12 pm

MadHatter
Administrator
From: Dallas TX
Registered: Jun 2006
Posts: 529
Website

Re: Advanced PHP Includes

have you tried posting that query into query browser and running it from there?

if you can export your table structure, and email it and your script I'll take a look at it.  I'll pm you my email address if you cant see it from here (I think mods see the email addresses where normal users just get a mail to form).

Offline

 

#35 29 Mar 2007 1:28 pm

Butcher
Moderator
From: Norway
Registered: Jul 2006
Posts: 308

Re: Advanced PHP Includes

Ran it by the SQL query, and got this:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id='1'' at line 1

Last edited by Butcher (29 Mar 2007 1:30 pm)


http://bamboocommandos.com/butcher_img/butchersig7.jpg

Offline

 

#36 29 Mar 2007 1:45 pm

MadHatter
Administrator
From: Dallas TX
Registered: Jun 2006
Posts: 529
Website

Re: Advanced PHP Includes

ah, yea, your id is an int datatype, and placing it in quotes makes it a string type (varchar, text, ... ), so remove the quotes from $id, and try again.

you should be able to get those errors in php too if you do a:

or die(mysql_error());

after each mysql function call (like mysql_connect, mysql_query and so on).

Offline

 

#37 30 Mar 2007 5:00 am

Butcher
Moderator
From: Norway
Registered: Jul 2006
Posts: 308

Re: Advanced PHP Includes

Ran the query with the PHPMyAdmin query page again, and got the same message:

Code:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id=1' at line 1

And this time there were no quotes by the id as you can see.


http://bamboocommandos.com/butcher_img/butchersig7.jpg

Offline

 

#38 30 Mar 2007 7:30 am

MadHatter
Administrator
From: Dallas TX
Registered: Jun 2006
Posts: 529
Website

Re: Advanced PHP Includes

get rid of the comma between $ip and Where: ip='$ip', WHERE ....

Offline

 

#39 30 Mar 2007 8:52 am

Butcher
Moderator
From: Norway
Registered: Jul 2006
Posts: 308

Re: Advanced PHP Includes

Hot damned, it worked! I always tought that WHERE was considered a seperate part of the columns because it actually is a column... PHP sure is cunning.


http://bamboocommandos.com/butcher_img/butchersig7.jpg

Offline

 

#40 30 Mar 2007 9:57 am

MadHatter
Administrator
From: Dallas TX
Registered: Jun 2006
Posts: 529
Website

Re: Advanced PHP Includes

cool

yea, by and large, you want to avoid naming your columns the same as sql keywords (select, from, where, set, limit, as, or... and so on). in most cases the sql parser will be smart enough to figure things out, but in the case where you have a column named where, and you're setting columns like that, the parser may think you meant to assign a column instead of declaring a conditional (column and table lists are comma separated, but you shouldn't have comma's anywhere in the sql syntax.

Offline

 

#41 30 Mar 2007 10:27 am

Butcher
Moderator
From: Norway
Registered: Jul 2006
Posts: 308

Re: Advanced PHP Includes

Ok, will do big_smile, finally works. By the way, you wouldnt happen to know how I can get a music player on a site without needing plugins and making it triggerable? I looked at the code for background music (figured I could get a button to trigger it to start somehow), but it only works for IE and Netscape, and most other methods require a plugin. Problems with plugins are that they have a habit of not working on all browsers or being disabled on a lot.


http://bamboocommandos.com/butcher_img/butchersig7.jpg

Offline

 

#42 30 Mar 2007 11:53 am

MadHatter
Administrator
From: Dallas TX
Registered: Jun 2006
Posts: 529
Website

Re: Advanced PHP Includes

usually flash is the preferred method for multimedia like that.  it requires a plugin, but since html doesn't have any direct support for sound you're left at the mercy of ie / moz / others in whether their browser will play the file you tell it to.

Offline

 



© 2003 - 2024 NullFX
Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License