[SOLVED] MySql & PHP Problem

[jadedknight]

I have a small problem with MySql & PHP. First off, I can connect to the database..and supposedly submit information through a form. Yet when I am in phpmyadmin, the table shows that their are records but no data can be seen.. so anyways I tried to display the information in my index.php page and all I get is "Resource id #4". If you can help I really appreciate it! Here is my code..

 

The form/process page.

<?php

require('db-connect.php');

$title = $_POST['title'];
$author = $_POST['author'];
$date = $_POST['date'];
$content = $_POST['content'];

$query = "INSERT INTO posts VALUES ('', '$title', '$author', '$date', '$content')";
mysql_query($query);
mysql_close();

?>

<form id="form-post" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">

<p>Title:</p>
<p><input class="required" id="field-title" type="text" name="title" /></p>
<p>Author:</p>
<p><input class="required" id="field-author" type="text" name="author" /></p>
<p>Date:</p>
<p><input class="required" id="field-date" type="text" name="date" /></p>
<p>Content: </p>
<p><textarea class="required" id="field-content" name="content" rows="5" cols="50"></textarea></p>
<p><input type="submit" value="Submit Post" /></p>
<p><input type="reset" value="Reset Fields" onclick="valid.reset(); return false" /></p>

</form>

 

The index.php page.

require('db-connect.php');

$query="SELECT * FROM posts";
$result=mysql_query($query);

echo $result;

[fert]

http://us2.php.net/manual/en/function.mysql-fetch-array.php

[pocobueno1388]

You didn't define the fields in your inser query...you need to do something like this:

 

$query = "INSERT INTO posts (col1, col2, col3, etc) VALUES ('', '$title', '$author', '$date', '$content')";

[jadedknight]

Ok I updated $query to..

$query = "INSERT INTO posts (id, title, author, date, content) VALUES ('', '$title', '$author', '$date', '$content')";

 

I then went to look into my database with phpmyadmin, and it says that there is only one record..so it is not yet submitting. Any suggestions? As for the link supplied by fert I read the manual, but I am not quite sure how to implement that code..

[pocobueno1388]

Was that record there before and did it actually hold the correct information instead of blanks? Are you still getting an error?

 

Try to catch the error:

 

mysql_query($query)or die(mysql_error());

[jadedknight]

Ok I put in the code suggested above now I get "Duplicate entry '0' for key 1" every time I load the form... ???

[simcoweb]

Is the 'id' field set to auto-increment? If so, you don't need to include it in your query as a field name.

 

$query = "INSERT INTO posts (id, title, author, date, content) VALUES ('', '$title', '$author', '$date', '$content')";

 

Should be:

 

$query = "INSERT INTO posts (title, author, date, content) VALUES ('', '$title', '$author', '$date', '$content')";

[jadedknight]

Thanks for all the fast replies! Yet with simcoweb's help, I now get "Column count doesn't match value count at row 1" *sigh* lol.

[Barand]

5 values and only 4 columns !

 

try

$query = "INSERT INTO posts (title, author, date, content) VALUES ('$title', '$author', '$date', '$content')";

[simcoweb]

Yeah, that first set of empty quotes isn't always necessary in your values.

[jadedknight]

Whew...ok fixed that now we are back to "Duplicate entry '0' for key 1". As an idea I deleted the posts table..and re entered the information into the form and I still get "Duplicate entry '0' for key 1"

[simcoweb]

You didn't answer the question about the 'id' field being auto-incremented.

[jadedknight]

Ah sorry. Yes it is auto-incremented, also it is the primary key.

[pocobueno1388]

EMPTY your entire posts table and there is no way you could get that error. Post the code you have now.

[Barand]

Assuming the columns in your table are defined in the order "id, title, author, date, content" then these are also viable alternatives

 

$query = "INSERT INTO posts (id, title, author, date, content) VALUES (NULL, '$title', '$author', '$date', '$content')";

 

$query = "INSERT INTO posts  VALUES (NULL, '$title', '$author', '$date', '$content')";

 

Do you have any other unique keys defined?

[pocobueno1388]

If it is a primary key that information can only exist once for that row as it should be unique. So you can't insert an identicle number or text for it...so take the primary key off, unless of course it is auto-increment.

[Barand]

As long as you exclude the PK column from your insert query, or give it a null value, then it should always generate the next unique id

[jadedknight]

Ok the id is auto-increment and its the primary key. This is the code I am using.

 

<?php

require('db-connect.php');

$title = $_POST['title'];
$author = $_POST['author'];
$date = $_POST['date'];
$content = $_POST['content'];

$query = "INSERT INTO posts (title, author, date, content) VALUES ('$title', '$author', '$date', '$content')";
mysql_query($query)or die(mysql_error());
mysql_close();

?>

<form id="form-post" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">

<p>Title:</p>
<p><input class="required" id="field-title" type="text" name="title" /></p>
<p>Author:</p>
<p><input class="required" id="field-author" type="text" name="author" /></p>
<p>Date:</p>
<p><input class="required" id="field-date" type="text" name="date" /></p>
<p>Content: </p>
<p><textarea class="required" id="field-content" name="content" rows="5" cols="50"></textarea></p>
<p><input type="submit" value="Submit Post" /></p>
<p><input type="reset" value="Reset Fields" onclick="valid.reset(); return false" /></p>

</form>

 

I cleared the table as previously stated and I still get "Duplicate entry '0' for key 1" id is set as Not Null and I tried with Null and it still gives me the error.

[pocobueno1388]

What other column do you have set as a primary key? Thats the problem.

[jadedknight]

Ok! I fixed the insertion issue, I accidentally didn't set id to auto-increment after following an example sorry, but I still can't get the post to display..this is my display code.

 

require('db-connect.php');

 

$query="SELECT * FROM posts";

$result=mysql_query($query);

 

echo $result;

 

Thanks so much for everyones help, I am just starting to learn php and I really like it.

[pocobueno1388]

<?php

$query="SELECT * FROM posts";
$result=mysql_query($query);

$row = mysql_fetch_assoc($result);

echo $row['col_name'];

?>

[Barand]

Here's my Table2Table.php script

 

<?php
include 'db.php';    //connnection stuff

function table2Table($tname) {
    $result = mysql_query("SELECT * FROM `$tname` ");

    $str = "<TABLE border='1' cellpadding='4'>\n";

    // column headings
          $str .=  "<tr>\n";
          while ($fld = mysql_fetch_field ($result)) {
                   $str .= "<th>{$fld->name}</th>\n";
          }
          $str .=  "</tr>\n";
      #    echo "</tr>\n";

    // list data
          while ($row = mysql_fetch_row($result)) {
          $str .=  "<tr>\n";
          foreach ($row as $field) {
                   $str .=  "<td>$field</td>\n";
          }
          $str .=  "</tr>\n";
    }

    $str .=  "</TABLE>\n";
    
    return $str;
}

// call function
echo table2Table('posts');
?>

[jadedknight]

<?php

$query="SELECT * FROM posts";
$result=mysql_query($query);

$row = mysql_fetch_assoc($result);

echo $row['col_name'];

?>

 

That gives me a blank page.. is it because the first id in the database is blank/null/nothing in it

[pocobueno1388]

You need to change $row['col_name'] to whatever fits your database...like $row['author']

[pocobueno1388]

You also don't have a condition for your query...so it isn't going to know exactly what to display.

 

Try this:

 

<?php

$query="SELECT * FROM posts";
$result=mysql_query($query);

while ($row = mysql_fetch_assoc($result)){

echo $row['col_name'].'<br>';

}

?>