 |  |  |  |  |  |  |  |
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 |
Latest PHP Posts
- Today's Posts
- Unanswered Threads
Related Forum Features
Tag cloud for PHP
.htaccess access ajax apache api archive array beginner binary broken cakephp checkbox class clients cms code cron curl database date directory display download dynamic echo email error errors file files folder form forms function functions google href htaccess html if...loop image include insert integration ip java javascript joomla limit link login loop mail menu mlm mod_rewrite multiple mysql oop parse paypal pdf php phpincludeissue problem query radio random recursion recursive regex remote script search server sessions shot smarty sms soap source space speed sql structure syntax system table tutorial update upload url validation validator variable vbulletin video web xml youtube
