Cant write to this MySQL table

Butters

Butters

Butters

Permabanned
Joined
22 Apr 2007
Posts
1,805
Although my php programming sucks, I think that by now I can write to a database ok as my enquiry and contact form works.

I want to write to this database from another form but it never updates:-

here is the database:

11280051ff5.jpg


Why doesn't this work?
 
You'd need to give your PHP code where you're actually performing the sql query for somebody to easily see what's wrong. The database structure itself that you posted above is fine.
 
OK, yeah,

Here is the php

Code: 
<?php
$p_title = $_POST['title'];
$p_menu_title = $_POST['menutitle'];
$p_summary = $_POST['summary'];
$p_body = $_POST['body'];

$address = "localhost";
$username = "mark1e_bourne";
$password = "******";
$database = "mark1e_bourne";

mysql_connect($address,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query = "INSERT INTO news VALUES ('$p_title','$p_menu_title','$p_summary','$p_body')";
print($query);
mysql_query($query);

mysql_close();



?>

And here is the html

Code: 
<html>
<body>
<form action="" method="post">
<p>Title - <input type="text" name="title" /></p>
<p>Menu Title - <input type="text" name="menutitle" /></p>
<p>Summary - <input type="text" name="summary" /></p>
<p>Body - <input type="text" name="body" /></p>
<p><input type="submit" name="submit" /></p>

</form>
</body>
</html>

Ta
 
Ok, I haven't used PHP for quite some time so somebody please correct me if I'm wrong but I believe unless you specify values for all of the fields in the table, or specify which fields you have data for it won't work, and that's why you're having issues.

So if you change the query to:

Code: 
$query = "INSERT INTO news (p_title, p_menu_title, p_summary, p_body) VALUES ('$p_title','$p_menu_title','$p_summary','$p_body')";

It should work fine. Also be careful if you're using that code on a live website because at the moment it's vulnerable to sql injection.
 
Add NULL to the Values so:

$query = "INSERT INTO news (id, p_title, p_menu_title, p_summary, p_body) VALUES (NULL, '$p_title','$p_menu_title','$p_summary','$p_body')";

That "Should" work.
 
putting single quotes around variable names will not work

insert into news (col1,col2,col3) values ($var1,$var2,$var3)

you don't have to put the primary key field in because it'll assume null if you don't

I really do recommend you doing some tutorials, Butters - they will teach you all the stuff you've been asking for the last few days
 
putting single quotes around variable names will not work

insert into news (col1,col2,col3) values ($var1,$var2,$var3)

you don't have to put the primary key field in because it'll assume null if you don't
Click to expand...

Putting single quotes around variable names in a sql query - like (s)he's done will work. Infact, unless the field is of INT (or another numeric only) type if you don't put them you'll get a mysql syntax error.

True about the primary key field though.
 
Thanks guys, Markus, your way has worked fine.

Thanks sic too, I know there are tutorials available but its working out how to describe what to search for. I should probably get a book or something.

One other thing I want to do, as you have probably guessed, I am using a mini CMS to update a news page and it works.

I write to the sql database using the form above and another page queries the SQL and displays the results.

However, what I want to do is have a small section of the news with a link. Such as:-

News Flash. News page is updated.....(full story here)

Where the user clicks full story and is taken to the article.

Any ideas?
 
Here's a very quick example of what I think you mean, this will display a complete list of news in

"title" - "first 50 chars of p_body"... (fully story here)

format and then, if they click the fully story here link it'll just display the complete p_body and title:

news.php
Code: 
$address = "localhost";
$username = "mark1e_bourne";
$password = "******";
$database = "mark1e_bourne";

mysql_connect($address,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$newsID = false;
if (!empty($_GET['id']) && is_numeric($_GET['id']))
{
	$qry = mysql_query("SELECT * FROM news WHERE id='".mysql_real_escape_string($_GET['id'])."'");
	if (mysql_num_rows($qry) == 1)
		$newsID = true;
}

if (!$newsID)
{
	//List of news - but truncated to 50 chars.
	$qry = mysql_query("SELECT * FROM news");
	while ($obj = mysql_fetch_object($qry))
	{
		echo($obj->p_title . " - " . substr($obj->p_body, 0, 50) . "... <a href=\"news.php?id=$obj->id\">(full story here)</a><br />");
	}
}
else
{
	//Full News Post
	$newsObj = mysql_fetch_object($qry);
	echo($newsObj->p_title . "<br />" . $newsObj->p_body);
}
mysql_close();

Obviously that won't do everything you want (or be coded perfectly) since it was just a quick example for you to work with.
 
Last edited:
Markus,

When I submit the news form TO the database it enters a blank row into the table as well as the row with the correct data.

Why is this?
 
Since you only have one query it definitely shouldn't do that. What I guess is happening is that you have the insert code, then the form. So that would cause it to insert a empty row then display the form, and then insert another row containing the valid data when you submit it.

You can prevent that by checking the form exists, I.e:

Code: 
if ($_SERVER['REQUEST_METHOD'] == 'POST')
{
  //php code to submit to the database...
}
else
{
  //display the form...
}

So it would look like this:

Code: 
<?php

if (isset($_GET['title']))
{
	$address = "localhost";
	$username = "mark1e_bourne";
	$password = "******";
	$database = "mark1e_bourne";

	mysql_connect($address,$username,$password);
	@mysql_select_db($database) or die( "Unable to select database");

	$p_title = mysql_real_escape_string($_POST['title']);
	$p_menu_title = mysql_real_escape_string($_POST['menutitle']);
	$p_summary = mysql_real_escape_string($_POST['summary']);
	$p_body = mysql_real_escape_string($_POST['body']);

	$query = "INSERT INTO news (p_title, p_menu_title, p_summary, p_body) VALUES ('$p_title','$p_menu_title','$p_summary','$p_body')";
	print($query);
	mysql_query($query) or die(mysql_error());

	mysql_close();
}

else
{
?>
<html>
<body>
<form action="save.php" method="post">
<p>Title - <input type="text" name="title" /></p>
<p>Menu Title - <input type="text" name="menutitle" /></p>
<p>Summary - <input type="text" name="summary" /></p>
<p>Body - <input type="text" name="body" /></p>
<p><input type="submit" name="submit" /></p>

</form>
</body>
</html>
<?php
}
?>

Alternatively of course you could have news.html with the form and the action to news.php that will post it.

That's my guess at what's happening to you anyway. Hard to tell without seeing the complete code of your file.
 
Last edited:
Cool, thanks.

One other thing. In the part above where you gave me the code for the news preview, I'm trying to alter so that the preview shows the user the p_summary and the (click here for full story) shows them the p_body (which it does already).

The only other two things I'm having trouble with are:

inserting the date above the summary (in dd month yyyy format (i.e. 22 November 2007)
and also making it so that the latest id increment shows on top of the previous news article not below it (if that makes sense).
 
To display the summary instead replace

Code: 
substr($obj->p_body, 0, 50)

with

Code: 
$obj->p_summary

---

To order by newest first change (in news.php)

Code: 
$qry = mysql_query("SELECT * FROM news");

To

Code: 
$qry = mysql_query("SELECT * FROM news ORDER BY id DESC");

---

To display the date you're going to have to add a new field to the database table (`date` INT) then when inserting put time(); into that column, and then when displaying you can use date('j F Y', $obj->date); to get the '22 November 2007' format.
 
Cool, thanks

When you say "inserting" and "displaying" do you mean altering the code of the input form and the news.php code?

also, quickly (i hope). I want the 'full story' link to appear directly beneath the summary. Is that possible?

Also, how can I get the code to conform to my CSS? as the link formatting of "full story here" is the default HTML link colours.

Thanks again
 
Markus123 said:
Putting single quotes around variable names in a sql query - like (s)he's done will work. Infact, unless the field is of INT (or another numeric only) type if you don't put them you'll get a mysql syntax error.

True about the primary key field though.
Click to expand...

ha yeah! what an idiot - don't know what I was thinking when I wrote that bit! sorry if I caused you any confusion there Butters :/

I posted a link to some decent tutorials in one of your other threads - the website's called tizag. I don't think you really need a book, unless you find learning from a book easier than screen :)
 
You must log in or register to reply here.
Back
Top Bottom