 |  |  |  |  |  |  |  |
retrieving a particular value with a sql query
Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases
 |
for some reason or another, my AUTO_INCREMENT command won't work in my sql table for the column named 'ID'. i have no idea why. it SHOULD work, but it doesn't. anyway, that's not really much of an issue. however, i have decided to take this opportunity to learn something new with PHP (i'm a noob). i was wondering how to use the MAX() command in my sql query.
i'm building a part of a CMS where the admin will be able to insert, retrieve, and update values from sql tables in my database. each time there is a row of values inserted, i'd like to retrieve the value of the largest number in the 'ID' column, and increment it, THEN insert that value AND the rest of the values that the admin wants to place in the table. i'm pretty sure i've got the logic of the idea correct, but i'm not exactly sure how to properly execute the code. here's what i have so far.
[php]
<?php
$sql = "SELECT incID FROM my_reg_table MAX(incID)";
$inc_regTable = mysql_query($sql);
$reg = "SELECT * FROM my_reg_table";
$result = mysql_query($reg);
$num_rows = mysql_fetch_array($result);
if($num_rows < 1){$incID = 0;}
else{$incID = $inc_regTable++;}
$reg_table = "INSERT INTO my_reg_table (incID, uID, category, item, qty_req, still_needs) ".
"VALUES('$incID', '$uID', NULL, NULL, NULL)";
mysql_query($reg_table);
?>
[/php] like i said i'm pretty sure the logic is correct. altho i'm not sure if i need to use the WHERE command before the MAX() function.
a couple of questions... if i'm using the MAX() function within a sql query, does the column type have to be INT? can it be VARCHAR? why or why not? second, in order to properly retrieve the MAX value of the column 'ID' do i have to use mysql_fetch_array()?? thanks in advanced for all help.
i'm building a part of a CMS where the admin will be able to insert, retrieve, and update values from sql tables in my database. each time there is a row of values inserted, i'd like to retrieve the value of the largest number in the 'ID' column, and increment it, THEN insert that value AND the rest of the values that the admin wants to place in the table. i'm pretty sure i've got the logic of the idea correct, but i'm not exactly sure how to properly execute the code. here's what i have so far.
[php]
<?php
$sql = "SELECT incID FROM my_reg_table MAX(incID)";
$inc_regTable = mysql_query($sql);
$reg = "SELECT * FROM my_reg_table";
$result = mysql_query($reg);
$num_rows = mysql_fetch_array($result);
if($num_rows < 1){$incID = 0;}
else{$incID = $inc_regTable++;}
$reg_table = "INSERT INTO my_reg_table (incID, uID, category, item, qty_req, still_needs) ".
"VALUES('$incID', '$uID', NULL, NULL, NULL)";
mysql_query($reg_table);
?>
[/php] like i said i'm pretty sure the logic is correct. altho i'm not sure if i need to use the WHERE command before the MAX() function.
a couple of questions... if i'm using the MAX() function within a sql query, does the column type have to be INT? can it be VARCHAR? why or why not? second, in order to properly retrieve the MAX value of the column 'ID' do i have to use mysql_fetch_array()?? thanks in advanced for all help.
Last edited by boo_lolly; Nov 28th, 2006 at 3:35 pm.
•
•
•
•
Originally Posted by boo_lolly 
for some reason or another, my AUTO_INCREMENT command won't work in my sql table for the column named 'ID'. i have no idea why. it SHOULD work, but it doesn't. anyway, that's not really much of an issue. however, i have decided to take this opportunity to learn something new with PHP (i'm a noob). i was wondering how to use the MAX() command in my sql query.
i'm building a part of a CMS where the admin will be able to insert, retrieve, and update values from sql tables in my database. each time there is a row of values inserted, i'd like to retrieve the value of the largest number in the 'ID' column, and increment it, THEN insert that value AND the rest of the values that the admin wants to place in the table. i'm pretty sure i've got the logic of the idea correct, but i'm not exactly sure how to properly execute the code. here's what i have so far.
[php]
<?php
$sql = "SELECT incID FROM my_reg_table MAX(incID)";
$inc_regTable = mysql_query($sql);
$reg = "SELECT * FROM my_reg_table";
$result = mysql_query($reg);
$num_rows = mysql_fetch_array($result);
if($num_rows < 1){$incID = 0;}
else{$incID = $inc_regTable++;}
$reg_table = "INSERT INTO my_reg_table (incID, uID, category, item, qty_req, still_needs) ".
"VALUES('$incID', '$uID', NULL, NULL, NULL)";
mysql_query($reg_table);
?>
[/php] like i said i'm pretty sure the logic is correct. altho i'm not sure if i need to use the WHERE command before the MAX() function.
a couple of questions... if i'm using the MAX() function within a sql query, does the column type have to be INT? can it be VARCHAR? why or why not? second, in order to properly retrieve the MAX value of the column 'ID' do i have to use mysql_fetch_array()?? thanks in advanced for all help.
are you trying to put data on textboxes... this is the code:
:cheesy:
First Form: create an html form
<form action="insertemployee.php" method="post">
<table width="425" border=1 align=center>
<caption><b>Please Fill Out Each Text Box!</b></caption>
<tr>
<td width="225" align="right" valign="middle">emp_id:</td>
<td width="200" align="center" valign="middle"><input type="Text" name="emp_id"><br></td>
</tr>
<tr>
<td width="225" align="right" valign="middle">f_name:</td>
<td width="200" align="center" valign="middle"><input type="Text" name="f_name"><br></td>
</tr>
<tr>
<td width="225" align="right" valign="middle">l_name</td>
<td width="200" align="center" valign="middle"><input type="Text" name="l_name"><br></td>
</tr>
<tr>
<td width="225" align="right" valign="middle">title</td>
<td width="200" align="center" valign="middle"><input type="Text" name="title"><br></td>
</tr>
<tr>
<td width="225" align="right">age:</td>
<td width="200" align="center"><input type="Text" name="age"><br></td>
</tr>
<tr>
<td width="225" align="right" valign="top">yos</td>
<td width="200" align="center" valign="middle"><input type="Text" name="yos"><br></td>
</tr>
<tr>
<td width="225" align="right" valign="top">Salary:</td>
<td width="200" align="center" valign="bottom"><input type="Text" name="salary"><br></td>
</tr>
<tr>
<td width="225" align="right" valign="middle">perks:</td>
<td width="200" align="center" valign="middle"><input type="Text" name="perks"><br></td>
</tr>
<tr>
<td width="225" align="right" valign="middle">email:</td>
<td width="200" align="center" valign="middle"><input type="Text" name="email"><br></td>
</tr>
<tr>
<td
colspan=2 align="center" valign="middle">
<input type="submit" name="submit" value="Enter information">
</form>
<form action="search1.php" method="POST">
<input type="submit" border="0" alt="search"
title="search" name="search" value="search">
</form>
<form action="delete1.php" method="post">
<input type="submit" border="0" alt="delete"
title="delete" name="delete" value="delete">
<?
</table>
<html>
<body>
then your sql query:
<?php
$con = mysql_connect("localhost","root");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("employees", $con);
$sql="INSERT INTO employee_data (emp_id,f_name,l_name,title,age,yos,salary,perks,email)
Values('$_POST[emp_id]','$_POST[f_name]','$_POST[l_name]','$_POST[title]','$_POST[age]','$_POST[yos]','$_POST[salary]','$_POST[perks]','$_POST[email]')";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "1 record added";
mysql_close($con);
?>
i hope this helps
please if you have any questions please pm me or ym or email me at hazel_0821@yahoo.com
|
Similar Threads
- C# VS 2005 - SQL Query Parameters to an ODBC DataSource (C#)
- sql query updating problem (Visual Basic 4 / 5 / 6)
- Javascript array from sql query (JSP)
- Please help me out with MySQL query (MySQL)
- PHP/SQL query help (PHP)
- Retreiving variables from a sql query into a form (PHP)
Other Threads in the PHP Forum
- Previous Thread: How would I write this asp code to work in PHP
- Next Thread: it's simple but yet complicated...php and mysql search..
| Thread Tools | Search this Thread |
You need to join our community in order to build your list of favorite forums. You can visit the forum index for a listing of all forums.
Related Forum Features
Latest PHP Posts
- Today's Posts
- Unanswered Threads
apache api array basics beginner binary broken buttons cakephp checkbox class cms code codingproblem combobox cron curl database date datepart display dynamic echo email error file files filter folder form format forms forum function functions global google head href htaccess html image include insert ip javascript joomla limit link list login mail malfunctioning memmory memory menu mlm multiple mysql nodes number oop parameter parsing password paypal pdf php problem query radio random recourse recursiveloop remote script search seo server sessions snippet source sp space sql static syntax system table tutorial up-to-date update upload url validator variable video web webdesign youtube
