 |  |  |  |  |  |  |  |
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
.htaccess apache api array autocomplete beginner binary body broken cakephp class cms code convert cron curl database dataentry date date/time display duplicates dynamic ebooks email emptydisplayvalue error execute explodefunction file firstoptioninphpdroplist folder form forms function functions google hack href htaccess html htmlspecialchars image include ip javasciptvalidation javascript joomla keywords limit link login mail matching mediawiki menu methods multiple mycodeisbad mysql network number object oop paypal pdf php phpincludeissue query random recursive redirect remote script search securephp server sessions shot source sp space speed sql subdomain subscription system table tag tutorial tutorials upload url validator variable vbulletin video web white youtube