php+mysql is killing me

[xux]

:-[ hi,
        I Need help and it is urgent,I want to collect email from a form and save to a database and later output the collected emails in a page(meant for a small website )but I cant just view it,I later went to run select* with phpMyAdmin and I discovered that the email column is empty.Please help me out  here are the codes below
to collect from the form[code]

<?php
include('header1.tpl');
    $email=$HTTP_POST_VARS['email'];
   
 
// open connection to MySQL server
$connection = mysql_pconnect('localhost', '', '')
or die ('Unable to connect!');
// select database for use
mysql_select_db('db') or die ('Unable to select database!');
$query="INSERT INTO newsletter(email) VALUES ('$email')";
$result=mysql_query($query);
if ($result)
{
echo'<br/>';
echo'<center>';
echo 'Thank you for signing up for smeresources newsletter';
echo'</center>';
}
else
{
echo'<br/>';
echo'<center>';
echo 'You cant be Registered,try again later';
echo'</center>';

}

?>
</div>


[/code]
here is the code to display the registered emails
[code]

<?php
include('header1.tpl');
// connecting to MySQL server
$connection = mysql_connect('localhost', '', '')
or die ('Unable to connect!');
// selecting database for use
mysql_select_db('db') or die ('Unable to select database!');
// create and execute query
$query = "SELECT *  FROM newsletter";
$result = mysql_query($query)
or die ('Error in query: $query. ' . mysql_error());
// check if records were returned
if (mysql_num_rows($result) > 0)
{
// print HTML table
echo'<br/>';
echo '<table width=80% cellpadding=10 cellspacing=0 border=1 align=center>';
echo
'<tr><td><b><center>Email Address of Subscribers</center></b></td></tr>';
echo '<ul>';
// iterate over record set
// print each field
while($row = mysql_fetch_array($result)) // using mysql_fetch_row
{
// prints in format "email"
echo "<tr><td>";
echo $row['id'];
echo "</td><td>";
echo $row["email"];
echo "</td></tr>";
}
echo'</ul>';
echo '</table>';
}
else
{
// print error message
echo 'No rows found!';
}
// once processing is complete
// free result set
mysql_free_result($result);
// close connection to MySQL server
mysql_close($connection);
?>

[/code]

here is the sql for the table
[code]
create TABLE newsletter(
email varchar(60) not null
);
[/code]
I will appreciate your assistance.Thanks in advance

[Destruction]

Question: why using mysql_fetch_array then using $row as an associative array?  You need to use mysql_fetch_assoc($result) or mysql_fetch_array($result, MYSQL_ASSOC) (I believe, you may want to check the MYSQL_ASSOC though as I'm going from memory from a fair while ago).  Also, your database schema does not have an ID column, so why are you calling $row['id'] ?

If you correct these, it should work for you,

Hope this helps,

Dest

[HuggieBear]

Destruction is right about the 'id' column, it doesn't exist, as for the mysql_fetch_array(), there's nothing wrong with the way you have that.  It will return both a numeric and associative array.  I don't know what the performance overhead on that is, but I always use mysql_fetch_array($result, MYSQL_ASSOC) as opposed to on its own.

I believe it's faster to retrieve as just a numeric array, but I can't remember (MYSQL_NUM).

Also, your code looks ok, so can you post your form.

Regards
Huggie

[xux]

Thanks,
          Here is my form
[code]
<form method="post" action="signup.php" style="display:inline;">
Enter Email:<br />
<input type="text" size="20" style="font-size: 10px;"  /><br />
<img src="images/spacer.gif" width="1" height="9" /><br/>
<a href="signup.php?a=subscribe"><img src="images/buttons_r1_c1.gif" width="74" height="18" border="0" alt="Submit" /></a>
</form>

[/code]
After rectifying the id column in the mysql,it is just outputting numbers and leaving the email section blank.whatelse do you think can be done?Thanks in advance

[HuggieBear]

Your problem is in your form...

Change this:
[code]<input type="text" size="20" style="font-size: 10px;"  />[/code]

To this:
[code]<input type="text" name="email" size="20" style="font-size: 10px;"  />[/code]

You didn't put the name in the form, so this line: [code=php:0]$email=$HTTP_POST_VARS['email'];[/code] is looking for a field named 'email' and it isn't getting passed one.

Regards
Huggie

[xux]

Hi,
  After Implementing the change it is still not displaying well.I later realised that the problem is from the intake maybe the signup.php has the problem (cos I have fixed the form),so what do you suggest?
My Regards

[HuggieBear]

What version of PHP are you using?

Regards
Huggie

[xux]

version 4

[HuggieBear]

Version 4 what?

Version 4.1.0, or 4.3.2 or something else?

Regards
Huggie

[xux]

4.1.0

[xux]

Huggie,
        I actually populated the database from the phpMyAdmin but the subsrciber file cant display the data and am even suspecting that the emails are not even getting inserted from the form too even after the modification you suggested.Thanks in advance for your help.
My Regards,
XUX

[xux]

Huggie,
        I got to go,but I wil back online probably in some hours.Thanks for your help

[HuggieBear]

Give this a try:

[code]<?php
include('header1.tpl');

$email = $_POST['email'];
   
// open connection to MySQL server
$connection = mysql_pconnect('localhost', '', '') or die ('Unable to connect!');

// select database for use
mysql_select_db('db') or die ('Unable to select database!');

// Run the query and get the result
$query = "INSERT INTO newsletter(email) VALUES ('$email')";
$result = mysql_query($query) or die ("Unable to run the following query:<br>\n$query<br>\n" . mysql_error());
if ($result){
  echo'<br/>';
  echo'<center>';
  echo 'Thank you for signing up for smeresources newsletter';
  echo'</center>';
}
else {
  echo'<br/>';
  echo'<center>';
  echo 'You cant be Registered,try again later';
  echo'</center>';
}
?>[/code]

[xux]

Hi,
    I tried out your suggestion but it wasnt populating the database out when I run select* query on phpMyAdmin is was outputing the id with out any email.Thanks
My Regards

[HuggieBear]

In that case it's your form...

Can you export the table structure from phpMyAdmin by using the export tab, and then post it here along with the code and I'll get on the case.

Regards
Huggie

[xux]

hi huggie,
              Hi,I try exporting the schema of the table but I dont know which format I should use to export it.One interesting thing happened,I tried to use insert statement to move data into the database but it was complaining of an error but when it was very obvious that it is error free.So I will really appreciate it if you can tell me how I should set the table or which format I should export the table schema so I can move it down here ?thanks

[HuggieBear]

OK, on the left hand side where you select the tables to export, select type is sql.  On the right hand side, where you have the sql options, select export type INSERT from the drop down.

Regards
Huggie

[xux]

Hi,
  Huggie I finally got it done.the codes are below
[code]
-- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 27, 2006 at 07:41 AM
-- Server version: 4.1.21
-- PHP Version: 4.4.2
--
-- Database: `sme_sme2`
--

-- --------------------------------------------------------

--
-- Table structure for table `newsletter`
--

CREATE TABLE `newsletter` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `email` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `newsletter`
--

INSERT INTO `newsletter` VALUES (1, '');
INSERT INTO `newsletter` VALUES (2, '');
INSERT INTO `newsletter` VALUES (3, '');

[/code]
I really appreciate your efforts.thanks and cheers
My Regards
XUX

[HuggieBear]

ok, table created, now can you post the code you're using, all php and (if separate) your form.

Regards
Huggie

[xux]

Hi,
    Thanks,here are the codes.first for the php file that will be triggered when the form is submitted i.e signup.php
[code]

<?php
include('header1.tpl');
    $email=$HTTP_POST_VARS['email'];
    $email=trim($email);
    $email=addslashes($email);
// open connection to MySQL server
$connection = mysql_pconnect('localhost', '', '')
or die ('Unable to connect!');
// select database for use
mysql_select_db('sme_sme2') or die ('Unable to select database!');
$query="INSERT INTO newsletter(id,email) VALUES ('','$email')";
$result=mysql_query($query);
if ($result)
{
echo'<br/>';
echo'<center>';
echo 'Thank you for signing up for smeresources newsletter';
echo $email;
echo'</center>';
}
else
{
echo'<br/>';
echo'<center>';
echo 'You cant be Registered,try again later';
echo'</center>';

}

?>
</div>

<?php
include('footer.tpl');
?>
[/code]
then here are the codes for the form
[code]
<div class="newsletter">
<img src="images/spacer.gif" width="1" height="30" /><br />
<form method="post" action="signup.php" style="display:inline;">
Enter Email:<br />
<input type="text" size="20"  name="email" style="font-size: 10px;"  /><br />
<img src="images/spacer.gif" width="1" height="9" /><br/>
<a href="signup.php?a=subscribe"><img src="images/buttons_r1_c1.gif" width="74" height="18" border="0" alt="Subscribe to Newsletter" /></a>
<img src="images/spacer.gif" width="6" height="1" />
<a href="#"><img src="images/buttons_r1_c3.gif" width="72" height="18" border="0" alt="Unsubscribe from Newsletter" /></a><br />
<img src="images/spacer.gif" width="1" height="5" />
</form>
</div>
[/code]
hope to hear from you soon.
My Regards
XUX

[HuggieBear]

OK, the problem you have here is that you're not actually submitting the form.  The moment you click on the link, it's navigating to signup.php as opposed to posting to it.  What you really needed was an image field in your form.

If you'd typed [code=php:0]echo $HTTP_POST_VARS['email'];[/code] at the top of signup.php, you'd have found that it didn't actually echo anything.

I'd change the code slightly if I were you.  Rather than have a button for subscribe and one for un-subscribe, just have one submit button.  So I enter my email address and hit submit, if I'm subscribed already then it un-subscribes me, if I'm not then it subscribes me.  How does that sound?

Regards
Huggie

[HuggieBear]

The following code has done away with your form, and just uses one file, signup.php, you should be able to call it signup.php and drop it onto your server as it is.  I'd advise backing up your existing code first in case you don't like this.

It uses the above concept of adding you if you don't exist, and removing you if you do exist.

Regards
Huggie

[code]
<?php
include('header1.tpl');

if (isset($HTTP_POST_VARS['email']) && !empty($HTTP_POST_VARS['email'])){
  $email = $HTTP_POST_VARS['email'];
  $email = trim($email);
  $email = addslashes($email);

  // open connection to MySQL server
  $connection = mysql_pconnect('localhost', '', '') or die ('Unable to connect!');

  // select database for use
  mysql_select_db('sme_sme2') or die ('Unable to select database!');

  // run query to see if email is subscribing or un-subscribing
  $query = "SELECT id FROM newsletter WHERE email = '$email'";
  $result = mysql_query($query) or die ("Unable to run query $query: " . mysql_error());
  $exists = mysql_num_rows($result);

  if ($exists == 0){
      // user doesn't exist
      $insert = "INSERT INTO newsletter (email) VALUES ('$email')";
      $result = mysql_query($insert) or die ("Unable to insert $insert: " . mysql_error());
     
      echo '<br/>';
      echo '<center>';
      echo "Thank you for signing up for smeresources newsletter, $email has been added to our list";
      echo '</center>';
  }
  else if ($exists == 1){
      // user exists
      $delete = "DELETE FROM newsletter WHERE email = '$email'";
      $result = mysql_query($delete) or die ("Unable to delete $delete: " . mysql_error());
     
      echo '<br/>';
      echo '<center>';
      echo "$email has been un-subscribed from the smeresources newsletter";
      echo '</center>';
  }
}

echo <<<HTML
<form method="post" action="{$_SERVER['PHP_SELF']}" style="display:inline;">
Enter Email:<br />
<input type="text" size="20"  name="email" style="font-size: 10px;"  /><br />
<input type="image" name="submit" align="absmiddle" src="images/buttons_r1_c1.gif" alt="Submit" /></a>
</form>
HTML;
?>

</div>

<?php
include('footer.tpl');
?>
[/code]

[xux]

Huggie,
        Thanks a Zillion,let me implement those changes and I will keep you inform.
My Regards
XUX ;D

[xux]

Huggie,
          There is a minor challenge.the form is part of a larger code and am wondering how to implement the changes.can you please break it down,i think it will be easier to implement.Thanks
My Regards
XUX

[HuggieBear]

Here you go:

[b][size=8pt]signup.php[/size][/b]
[code]<?php
include('header1.tpl');

if (isset($HTTP_POST_VARS['email']) && !empty($HTTP_POST_VARS['email'])){
  $email = $HTTP_POST_VARS['email'];
  $email = trim($email);
  $email = addslashes($email);

  // open connection to MySQL server
  $connection = mysql_pconnect('localhost', '', '') or die ('Unable to connect!');

  // select database for use
  mysql_select_db('sme_sme2') or die ('Unable to select database!');

  // run query to see if email is subscribing or un-subscribing
  $query = "SELECT id FROM newsletter WHERE email = '$email'";
  $result = mysql_query($query) or die ("Unable to run query $query: " . mysql_error());
  $exists = mysql_num_rows($result);

  if ($exists == 0){
      // user doesn't exist
      $insert = "INSERT INTO newsletter (email) VALUES ('$email')";
      $result = mysql_query($insert) or die ("Unable to insert $insert: " . mysql_error());
     
      echo '<br/>';
      echo '<center>';
      echo "Thank you for signing up for smeresources newsletter, $email has been added to our list";
      echo '</center>';
  }
  else if ($exists == 1){
      // user exists
      $delete = "DELETE FROM newsletter WHERE email = '$email'";
      $result = mysql_query($delete) or die ("Unable to delete $delete: " . mysql_error());
     
      echo '<br/>';
      echo '<center>';
      echo "$email has been un-subscribed from the smeresources newsletter";
      echo '</center>';
  }
}
else {
  header("Location: form.htm");
}
?>

</div>

<?php
include('footer.tpl');
?>[/code]

[b][size=8pt]form.htm[/size][/b]
[code]<form method="post" action="signup.php" style="display:inline;">
Enter Email:<br />
<input type="text" size="20"  name="email" style="font-size: 10px;"  /><br />
<input type="image" name="submit" align="absmiddle" src="images/buttons_r1_c1.gif" alt="Submit" /></a>
</form>[/code]