Help with simple DB connect and insert

FMCgenesis

Hello,

First time here while I was looking for references for a simple requirement. I know it's simple but I don't know how to code it myself - maybe you guys can help me out.

What I want is a html page that I can simply load in my browser;

Page requires a form with some fields; I'll post the SQL code that I need to issue from the web.

INSERT INTO `prefix_table1` VALUES ([COLOR=Red]7[/COLOR], '', '[COLOR=Red]7[/COLOR]', 'Clan Donation', '', '1', '', '[COLOR=Red]MEMBERNAME[/COLOR]', '0.00', 'Reveal Name?', 'Yes', '', '', '', 'Completed', '[COLOR=Red]2006-12-17 01:01:01[/COLOR]', 'web_accept', '[COLOR=Red]5.00[/COLOR]', '0.00', 'USD', '', '', '[COLOR=Red]FIRST[/COLOR]', '[COLOR=Red]LAST[/COLOR]', '', '', '', '', '', '', '[COLOR=Red]EMAIL[/COLOR]', '');

Items in red is what I need to be able to input in the form, then click on submit and it should import that entry into the database.

The number '7' in the code is the transaction number and needs to increment, but I can live without that... Everything else is manually put in by hand.

5.00 in the code is the amount, generally can change from 5.00 to something else.

I know for some of you it may be a 5 minute trick so please I'd like to get some help in this matter. I can provide more information if I know what's needed. So far I've only been able to succesfully establish a connection and disconnect.

Thanks
gen

theelectricwiz

If you want to use the command to insert something you need to use the mysql_query command

$conn = mysql_connect('localhost', $user, $pass);
mysql_select_db($db, $conn);
mysql_query("INSERT INTO `prefix_table1` VALUES (7, '', '7', 'Clan Donation', '', '1', '', 'MEMBERNAME', '0.00', 'Reveal Name?', 'Yes', '', '', '', 'Completed', '2006-12-17 01:01:01', 'web_accept', '5.00', '0.00', 'USD', '', '', 'FIRST', 'LAST', '', '', '', '', '', '', 'EMAIL', '')");
mysql_close($conn);

If you are want the transaction number to increment automatically you can leave it blank. Be careful with the version you of mysql you are using as if you have a setting set mysql will reject the '' values - instead use NULL.
To insert from a simple form like one in the code below use something like the php below that

<form action="belowphp.php" method="POST">
<input type="text" name="name" />
<input type="text" name="email" />
<input type="submit"  value="Submit" />
</form>

<?php
$conn = mysql_connect('localhost', $user, $pass);
mysql_select_db($db, $conn);
mysql_query("INSERT INTO `some_table` VALUES ('', $_POST[name], $_POST[email])");
mysql_close($conn);
?>

If you need any more info on the commands search for them on www.php.net
Hope this helps.

FMCgenesis

Thanks for the help, I think I can make it happen without any trouble...

Question about autoincrement... is it possible to fetch the highest number and pre-fill it in the form?

theelectricwiz

if you are wanting to prefill the box you will need a bit of sql in the form page. Something like below should do the trick:

$conn = mysql_connect('localhost', $user, $pass); 
mysql_select_db($db, $conn); 
$tran_cmd = mysql_query("SELECT id FROM table ORDER BY id DESC LIMIT 1"); 
$tran_id = mysql_result($tran_cmd, 0);

That would give the the id of the last inserted item if you are wanting the next one to be inserted simply add one
mysql_close($conn);

Piranha

It is much better to let the database handle the ids. Otherwise it may be that two forms will get the same id, and then you are in trouble.

Use [man]mysql_insert_id[/man] to get the autoincremented id, it should solve most of your problems.

FMCgenesis

Thanks for all the great help. Before I get into incrementing the transaction number, I'd like to post my code here because it seems to be working, but I don't get an insert in the database.

Index.htm

<html>

<head>
<meta http-equiv="Content-Language" content="en-us">
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Transaction Number</title>
</head>

<body>

<form action="submit.php" method="POST"><p>
  <br>
&nbsp;</p>
  <table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber1">
    <tr>
      <td width="21%" bgcolor="#CCCCCC">Transaction Number:</td>
      <td width="79%" bgcolor="#FFFFFF"><input type="text" name="id_txnid" size="20" /></td>
    </tr>
    <tr>
      <td width="21%">NickName:</td>
      <td width="79%" bgcolor="#FFFFFF"><input type="text" name="custom" size="20" /></td>
    </tr>
    <tr>
      <td width="21%" bgcolor="#CCCCCC">Reveal:</td>
      <td width="79%" bgcolor="#FFFFFF"><select size="1" name="option_selection1">
      <option selected>Yes</option>
      <option>No</option>
      </select></td>
    </tr>
    <tr>
      <td width="21%">Date &amp; Time:</td>
      <td width="79%" bgcolor="#FFFFFF">
      <input type="text" name="payment_date" size="20" value="2006-12-13 01:01:01" /></td>
    </tr>
    <tr>
      <td width="21%" bgcolor="#CCCCCC">Amount:</td>
      <td width="79%" bgcolor="#FFFFFF"><input type="text" name="mc_gross" size="20" value="5.00" /></td>
    </tr>
    <tr>
      <td width="21%">First Name:</td>
      <td width="79%" bgcolor="#FFFFFF">
      <input type="text" name="first_name" size="20" value="First" /></td>
    </tr>
    <tr>
      <td width="21%" bgcolor="#CCCCCC">Last Name:</td>
      <td width="79%" bgcolor="#FFFFFF">
      <input type="text" name="last_name" size="20" value="Last" /></td>
    </tr>
    <tr>
      <td width="21%">EMail:</td>
      <td width="79%" bgcolor="#FFFFFF"><input type="text" name="payer_email" size="20" /></td>
    </tr>
    <tr>
      <td width="21%" bgcolor="#CCCCCC">&nbsp;</td>
      <td width="79%" bgcolor="#FFFFFF">&nbsp;<input type="submit" value="Submit" name="B1">
      <input type="reset" value="Reset" name="B2" /></td>
    </tr>
  </table>
  <p>&nbsp;</p>
</form>

</body>

</html>

submit.php

<?php 
$conn = mysql_connect('localhost', '*****', '******'); 
mysql_select_db('gen_db', $conn); 
mysql_query("INSERT INTO `pnc_treasury_transactions` VALUES ($_POST[id_txnid], '', '$_POST[id_txnid]', 'EMK Clan Donation', '', '1', '', '$_POST[custom]', '0.00', 'Reveal Name?', 'Yes', '', '', '', 'Completed', '$_POST[payment_date]', 'web_accept', '$_POST[mc_gross]', '0.00', 'USD', '', '', '$_POST[first_name]', '$_POST[last_name]', '', '', '', '', '', '', '$_POST[payer_email]', ''");
if (!$conn) {
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($conn); 
?>

bradgrafelman

I wasn't sure if the username/pw you had in your code snippet was real or not, so I blanked it out anyway.

Debug the mysql_query() call - does it fail? If so, what is the output of [man]mysql_error/man ?
You're checking to see if the connection to SQL failed... after you attempt to execute a query? Doesn't make sense... I think you might want to flip the order of things there.
Array indeces in your case should be strings. $row[name] does not have a string as an index, but a constant. PHP will detect this (assuming there actually isn't a constant named 'name') and throw an E_WARNING and then assume it is a string. So, use quotes: $row['name'].
Arrays, more specifically items in arrays referenced by indeces, should not simply appear inside a double-quoted string. If they do, they should be wrapped in braces ( {} ). Instead, I usually escape out of the string and concatenate the array in the middle, like so:
```
echo 'Your name is: ' . $row['name'] . '! Hello there.';
```

FMCgenesis

No, the password is not real... but i'm lost. Don't think of me as a PHP coder at all, in fact when long time ago I tried some basic programming, I had to go through severe headaches... so I try to think as less as possible.

Can you modify my code up there?