PHP-MySQL: Returning ID When New Record Is Made?
Let's say I create a new record in a table like this:
mysql_query("INSERT INTO table (col1) VALUES ('example')",$conn);
....that had an auto-incrementing, unique identifying column named "ID"
that would be populated with a unique number upon insertion...
How can I get the unique number it assigned to that record returned for
futher use?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Different Result From Query Made In Mysql-front And From ASP
I have a strange problem with a mysql database and ASP. I try to retrieve a value from a table I have just created with a very simple sql statement. Ex. SELECT myValue FROM myTable WHERE userID = 'myName' If I type the query in mysql-front it works fine but if I execute it from and ASP page I get no value. I have tried something similar with other tables in my database without this problem. My ASP script looks like this: <% sql = "SELECT * FROM t_organisation_salg WHERE brugerID = '10025'" set objRS = objConn.execute(sql) IF NOT objRS.EOF THEN response.write objRS("omsaetning_gruppe") objRS.close set objRS=nothing objConn.close set objConn = nothing %> My database connection is included from another file.
Returning "binary Flags" With Found Record(s)
I am using the 'binary flag' principle for defining (multiple) modes for an element.. Elements table: id name flags 1 a 9 2 b 3 3 c 11 Flags table: id value name 1 1 mode 1 2 2 mode 2 3 4 mode 3 4 8 mode 4 Now.. Is it possible with one query to get all the modes listed?? to get a result like: 1 a mode1, mode 4 2 b mode 1, mode 2 3 c mode3, mode 4
Changes Made In One Connection Does Not Reveal In Another Connection At Once.
I use many connection and the same as many threads in my server app to gain performance on a multicpu machine. Firstlly i call "set autocommit=0" and wrap every statements with "start transaction " and "commit". i use mysql_stmt_execute for every statement rather than mysql_real_execute except those that can't be prepared. The version of mysql is 4.1.11 under Debian linux. In one connection of one THREAD i insert a row into a table. After i got the sucess code and the last insert id, i then do a "select" from another connection in another THREAD with the "last insert id" return by the previous insert . I expect the select can fetch the row that i have just insert , but some time it does and some time it doesn't. is there any funtion like "mysql_....flush...cache.." to make the changes from one connection to be reveal to the global at once?
Mysql Is Returning Empty Result Set Even When It Should Not
I recently moved a small access database to mysql. It appeared that everything was OK, but here is a problem I have just noticed. Few of the SELECT queries (depending on their WHERE conditions) are returning empty result set even when they should not. Very strange, wondering if anyone of you have faced a similar situation, where mysql returned empty result sets even when they shouldn't.
Mysql Query Returning Incorrectly
The following MySql Query is not retuning correctly. It is returning dates outside the given range. Why? Thanks. SELECT distinct sale_type_landsale.sale_type_landsale_id ,sale_type_landsale.sale_date,sale_type_landsale.city,sale_type_landsale.name,sale_type_landsale.sale_price,sale_type_landsale.number_lots FROM sale_type_landsale Left Outer Join connector_sale ON sale_type_landsale.sale_type_landsale_id = connector_sale.ref_id Left Outer Join sale ON connector_sale.sale_id = sale.sale_id Where sale_type_landsale.sale_date >= ��-01-01' AND sale_type_landsale.sale_date <= ��-06-08' AND sale_type_landsale.city =Ɗ' OR sale_type_landsale.city =ཉ' AND connector_sale.ref = 'sale_type_landsale' AND sale.sale_archive <> 1
MYSQL Statement Not Returning Data...
Is there something I am doing wrong in the following query: SELECT * FROM 'photos' WHERE approval='NULL' ORDER BY 'reg_date' ASC LIMIT $page, $limit". It doesn't seem to target the approval field in the photos table in which all current values are NULL... or am I targeting NULL incorrectly? The field is a tinyint(1) so it can be 0 for NO, 1 for YES, or NULL so that I can target photos that have not been checked yet. It runs fine but returns no results when it should return all current rows in the table.
Returning MySQL Blob To Page.
I have a blob field in a database and a type field. The blob will either contain a zip file or a gp3 or gp4 file. I need to return this blob so that the user can download the file. I can download a zip simply be setting header type to application/zip and returning the blob but how do I return the others, can I just put application/$type?
MySQL Returning Redundant Rows
I have 36 records in three tables, each row is related to one row in each table. Up until I add the last where condition (staff_descr.certs LIKE ...), it's fine. But with that last condition, it suddenly returns 108 rows, each row repeated 3 or 4 times. PHP Code: SELECT staff.obj_id , staff.timestamp , staff.full_name , staff.city , staff.state FROM `staff` , `staff_descr` LEFT JOIN `user` ON user.username = staff.username WHERE user.is_confirmed = 1 && staff_descr.certs LIKE '%Basic Lifeguarding%'
MySQL 5 Stored Procedures: Returning Prematurely
I would like to know whether it is possible to return prematurely from the body of a MySQL 5 procedure. It seems MySQL 5 only allows the RETURN keyword in PROCEDURES, so I end up having to solve this problem by using a whole bunch of nested IF statements. Is there a better way?
Looking Up Duplicate Record Or Adding New Unique Record
CREATE TABLE tbllayer ( LayerID int(11) NOT NULL default '0', LayerSize int(11) NOT NULL default '0', IceTypeID int(11) NOT NULL default '0', Fingerprint char(16) binary default NULL, PRIMARY KEY (LayerID), UNIQUE KEY Fingerprint (Fingerprint), KEY IceTypeID (IceTypeID) ) TYPE=MyISAM; We have an internet monitoring application which stores objects in the above table, with the fingerprint an MD4 of the object. In general about 30% of the time an object monitored is already in the table, in which case we don't want to re-insert it, we just want to find out it's ID. The percentage may vary between 10% and 50% though. Currently we have a cache in our application which works like this: - object is monitored and its fingerprint taken - is the fingerprint in the cache? if so, take its ID from there - if not, do a SELECT on the table - if a match is found add it to the cache and use its ID - if not, INSERT the record into the tablem use its ID and possibly add it to the cache too Ok. In general, is it better to: - do a SELECT to see if the record exists and if not INSERT it or - do an INSERT, and if it fails then do a SELECT to locate the record What about if the duplicate ratio is high or low?
Automatically Record Date Of Record Entry
I have my database table set up and I have an HTML form that is PHP driven that will add the information entered into the form into to my database table. I have a local buy-sell-trade Website. The way it has worked is that people fill out a form and the results emailed to me. I then take the information and enter it into a Web page. I only want the ads to be displayed for 30 days. I keep the ad for a total of 6 weeks (displays for 30 days and sits in limbo for 2 weeks afterwords) and if not renewed within that 2 week limbo period - I delete it. Entering all the ads and keeping up with the dates manually has become a burden. I've only recently began looking into databases. My hosting company provides me with phpmyadmin and mysql 5.0. I'm new to all of this but I have managed to set up a database table and a HTML form that is PHP driven that allows ads to be automatically added to the database table. There is a lot I need to do to make this ideal, but one step at a time. First, I need to know the date (March 02, 2006) the ad was created or added to the database table. I know that I need to add some piece of code to my php form to record this information, but what code and where do I put it? I know I will need to create an extra field in my database table to house the date - I can handle this. I've read the date and time information here http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html but there's a lot there and I don't know which is right for my needs. Plus, it doesn't tell me how or where to insert it into my php form (or does it?).
Looking Up Duplicate Record Or Adding New Unique Record
CREATE TABLE tbllayer ( LayerID int(11) NOT NULL default '0', LayerSize int(11) NOT NULL default '0', IceTypeID int(11) NOT NULL default '0', Fingerprint char(16) binary default NULL, PRIMARY KEY (LayerID), UNIQUE KEY Fingerprint (Fingerprint), KEY IceTypeID (IceTypeID) ) TYPE=MyISAM; We have an internet monitoring application which stores objects in the above table, with the fingerprint an MD4 of the object. In general about 30% of the time an object monitored is already in the table, in which case we don't want to re-insert it, we just want to find out it's ID. The percentage may vary between 10% and 50% though. Currently we have a cache in our application which works like this: - object is monitored and its fingerprint taken - is the fingerprint in the cache? if so, take its ID from there - if not, do a SELECT on the table - if a match is found add it to the cache and use its ID - if not, INSERT the record into the tablem use its ID and possibly add it to the cache too Ok. In general, is it better to: - do a SELECT to see if the record exists and if not INSERT it or - do an INSERT, and if it fails then do a SELECT to locate the record What about if the duplicate ratio is high or low?
Getting Record Before Or After The Record That Meets The Criteria
In a query I want to get all the results and then order those results by last name, but then I want to filter those results down to only the record that comes before or after the record that has 'empno' = '1259'. I want to get all the results already ordered and then filter them down to one record either before or after (depending on what is needed) the record where 'empno' = '1259'.
Delete A Record From The MySQL Db
I have the following code which doesn't work and I don't know why: <?php require_once ("ghreat/connector.php"); $db=mysql_select_db(DB_NAME); if (!$db) { echo "Could not change into the database"; exit; } if(isset($_POST['submit'])) { $query = "Delete FROM peopleTable WHERE personID='$personID"; } else { $query = "SELECT * FROM personTable WHERE personID='$radiobutton'"; $result = mysql_query($query) or die("Cannot perform patient record query."); $row = mysql_fetch_array($result); ?> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td> <div align="center"> The php page displays a single record from the database. It has a delete button at the bottom so when the administrator hits the button the record is supposed to be removed from the MySQL database. There is also a Submit button labelled "Closed" next to the "Delete" one.
MySQL Bytes Per Record
I am trying to do some calculations as the amount of hard drive space I am going to need. Basically I am looking to create a 318,934 x 8 array. The IEEE Standard 754 is common to C compilers (so I read). The numbers are real numbers small enough to use the single data type which occupies 4 bytes. MySQL uses 4 bytes as well to store floating point data. So does this mean MySQL will take 4 bytes for the floating point multiplied by 4 bytes for MySQL storage location? Also do any of these standards change when moving to 64-bits? IEEE STANDARD 754 single type = 4 bytes double type = 8 bytes extended type = 10 bytes IEEE Standard Column Type | Storage Required FLOAT(p) | 4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53 FLOAT | 4 bytes DOUBLE [PRECISION], item REAL | 8 bytes DECIMAL(M,D), NUMERIC(M,D) | M+2 bytes if D > 0, M+1 bytes if D = 0 (D+2, if M < D) 11.5. Column Type Storage Requirements.
Record Number In Mysql
I'm rusty and it's been a few years since I used mysql a lot. Is there a variable for the record number? for instance could a query be writen SELECT * FROM table WHERE "record number variable" > 1000
How To Find The Latest Record In MySQL?
Probably a really simple answer, but I have no idea on what to do... Basically I am displaying my table fine, but I would like to only display the last inserted record. So say the last insertion was id number 79, that's all I want to display?
Retrieve Smallest Record From Mysql
I have the following in a mysql DB ID Name Money 1 Kevin 20000 2 John 1000 is there a mysql query i can run that will Select the name of the person where money is the smallest value?
Can Mysql Join 2 Queries Into 1 Record Set?
if I cannot make a query work can i combine the results of 2 different queries into 1 record set? can we combine records from : SELECT RESTNAME AS SET1 FROM RESTAURANTS WHERE RESTCITY = '$city' SELECT RESTNAME AS SET2 FROM RESTAURANTS WHERE RESTZIP = '$zip' I know this is an easy example and we can combine them in 1 query, but for complicated purposes is there a way to combine both sets and sort the full set .... somthing like JOIN SET1 ON SET2 ???
How To Save Chinese Record In MySQL?
I am now using MySQL database to save Chinese, my OS if Chinese Windows2000, and my MYSQL version is 4.2, but I couldn't save some Chinese characters at all, some can be saved, others are lost or displayed in a mass, you won't understand the meaning at all. Can anyone tell me how to solve this problem, how to save Chinese informaiton in MySQL?
2-3 Million Record Table With MySQL
Does anyone have any experience working with very large tables? Say, 2-3 million records? I have the opportunity to work on a new project where at least one of the tables could grow to be that size, and I'm looking for any input on if MySQL is a good solution.
PHP + MySQL Updating All Record In Table
I need a loop that updates 2 fields of all the records in my table. I will multiply these fields by a value passed as POST to php. I'm having trouble with mysql_fetch_assoc and mYsql_fetch_array... How do I do this... all i have done so far is selecting the fields from the table and outputting to the browser. How do I modify the values and update them in the right place?
Record Creates Record
I have a table that holds inventory information...primary key'd with an asset#. I have another table that I hold certain information in that I don't want in the main inventory table. I would like the second table to key itself and automatically enter a record when a device is entered. For example, I enter a scanner in my main table, but I want the DB to then also enter that assetNumber into the second table for me.
Insert A Record Using MySQL Query Browser
I know I can probably type in the actual query, but I was wondering if there was a means of inserting a record without typing "insert into table_name (blah, blah2) values (blah, blah)". I'm looking for a method as easy as typing into an Excel spreadsheet or Access DB. (I know I'm gonna get bashed for mentioning Microsoft products.)
Adding To An Existing MySQL Field/record
I want to be able to add to an existing record. I have a table call pages, and within this i have a column called audit_history. How can i run a query so that it adds content (stored in $newHistory) to the existing audit_history field. I don't want to have to retrieve the audit history first and then add it on using PHP as this seems a waste. Once the query has been run the audit_history should contain the existing data and the $newHistory data.
Returning More That One Row From SP
I have 2 simple questions on stored procedures: 1) how do I return multiple rows? DELIMITER // CREATE PROCEDURE join_nary_relation() BEGIN DECLARE c INT; SELECT clanak_id FROM vidi_clanak_hardver INTO c; END; // CALL join_nary_relation(); This fails: ERROR 1172 (42000): Result consisted of more than one row 2) how do I return multiple columns? As you see, I'd like to warp a SP around a SELECT in general case: a SELECT which returns a table with few rows and few columns. Is that possbile?
Returning Last Row
I need to return the last row in the table, I'm using auto_increment which is the 'priKey' column. 'beachName' is the name of the table , 'DATEOFFILE' is the column in the table that I need to return the last value for. Code:
Returning One Row
I'm working on a support ticket system. My two main tables are "tickets" and "ticket_messages". One ticket can have many ticket_messages. Ticket_messages have time stamps. I need a query that will return all tickets with the ticket_message of the earliest time stamp, as opposed to pulling all ticket_messages for each ticket. I'm having trouble digging up documentation or examples of this. Here's a pseudo SQL statement that I'm trying to accomplish: Code: SELECT DISTINCT tm.subject, t.id, t.incident_date_time, t.create_date_time, FROM tickets t INNER JOIN ticket_messages tm ON tm.ticket_id = t.id WHERE tm.id OF Min(tm.date_time_stamp)
Returning All Values
I'm sure this is a straight forward and obvious one: I have this: SELECT * FROM tbl_contacts WHERE con_Customer = "#URL.con_Customer#" Which obviously when you pass a value over the url returns the relevant records. How can I pass a value to the url which passes ALL the records back. Is it something like ?con_customer='%'?
AVG Returning 0, Not Null
I'm writing a script to allow visitors to rate articles on my site. Sometimes a question does not apply to that particular article or a person is just too lazy to fill out the entire form. Either way, I'm passing NULL to the MySQL database if the question was not filled out. I want to do averages for each article for each question I ask. The problem is that I'm getting 0 only when I use AVG () even though the MySQL site says that should return a null if empty. It is not. I have a "control group" table pulling the same data without using AVG. Of course, every vote is visible, but it's clear that null is working properly in that example. Here's is a shortened version of my PHP $queryA = " SELECT URL , AVG(Vote) ,AVG(A1) , AVG(A2) , AVG(A3) FROM Galleries GROUP BY URL "; $result = mysql_query($queryA) or die(mysql_error()); while($row = mysql_fetch_array($result)){ ?> <tr> <td><?php echo $row['AVG(A1)']; ?> </td> <td><?php echo $row['AVG(A2)']; ?> </td> <td><?php echo $row['AVG(A3)']; ?> </td> </tr> <? } echo "</table>"; Any thoughts as to why AVG() would return 0 and not null when my database seams to be setup correctly?
Returning A One Value Average
I'm using PHP and MySQL, and am trying to return an average (songle value). My code isn't working, and I'm getting confused between the mysql_fetch, here's my code: $average = "SELECT AVG(overall) as overall, AVG(gameplay) as gameplay, AVG(graphics) as graphics, AVG(sound) as sound FROM VIDEO_GAME_DETAIL WHERE name=" .$row['name']; $result = mysql_query($average); $rating = mysql_fetch_array($result); // down in an HTML table echo "<td>" .$rating['overall']. "</td>"; $row['name'] is the name of a PC Game from the VIDEO_GAME table. In the VIDEO_GAME_DETAIL table I have reviews for many games, with ratings. My goal here is to average the different ratings (overall, gameplay, sounds and graphics) for a one game, and stick them with the main page that lists the games (1 row per game). I get this following error: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /Users/Marconi/Grad/lleccia/www/ISYG250/proj4/reviews.php on line 54 line 54 is $rating = mysql_fetch_array($result);
Returning Most Relevant
I have a table with a text field which contains the information for help files on my website. Using full text searches i can return the most relevant results in order but is it possible to return the most relevant help text for each of the returned results. I'm trying to show the user a sentence or two as a preview for the returned results.
How Returning Zero With COUNT()?
I have this table: CREATE TABLE `people` ( `id` int(11) NOT NULL auto_increment, `name` varchar(100) NOT NULL default '', `sex` char(1) NOT NULL default 'M', `age` tinyint(4) NOT NULL default '0', PRIMARY KEY (`id`) ) TYPE=MyISAM; And I want knon how many Females and Males there are in three ranges of ages: from 0 to 20, from 21 to 30 and over 31. I've used COUNT() GROUP BY(sex) but the problem is that COUNT() doesn't return zero but I want see the exact count of peoples of every ranges of age (for sex) including zero. This is my query: SELECT sex, ' < 20' AS range, COUNT(*) AS Num FROM people WHERE age between 0 and 20 GROUP BY(sex) UNION SELECT sex, '21-30' AS range, COUNT(*) AS Num FROM people WHERE age between 21 and 30 GROUP BY(sex) UNION SELECT sex, ' > 30' AS range, COUNT(*) AS Num FROM people WHERE age > 30 GROUP BY(sex) How can I see zeros?
Returning The ID Of The Row Just Inserted...
I'm trying to amend my simple SP to return the unique 'auto-incremented' ID value to my SP as a client reference. The SP inserts the new DB record and then should return the ID for that row...however when I run the PHP page that calls the SP, I get: "ERROR: 1312 - PROCEDURE lead_collector.SPInsertEnquiry can't return a result set in the given context" My SP is as follows: Code:
Last_insert_id() Returning 0
I was using a stored function to return the last inserted record id. With the latest version of the database that functions appears to have stopped working. Is there a fix or a work around that I can use for the meantime ?
Query Returning
I'm having a small problem with my news system. The query below should return the last 5 posts, who posted them, and the number of comments for each. It works fine, except the value comments_count is returning is 7 times what it should actually be.
Returning To The Community
Recently the community helped me immensly when I had a problem with my SQL and the server, I can only feel indebted (is there such a word?) to them for their help.
Last_insert_id Returning 0
Im trying to something like the following: INSERT INTO foo (auto,text) VALUES(NULL,'text'); # generate ID by inserting NULL INSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID(),'text'); The first insert goes ok and a record in foo is created, but the id inserted into foo2 is 0 and thats not right.
Returning A Percentage
is it possable to return ONLY 20% percent of the overall possable returns in a query? e.g. i have 100 possable returns and i only want 20% of them. So i'd receive 20 returns. What i'm trying to do exactly is return the latest 30 entries and then the next 20% of the possable entries. i'm working with a database that has thousands of possable returns but i don't want all of them.
Returning New Columns
I have data in my SQL table like this: (it goes like this to March 2008) And I would like MySQL to return the same data but with this structure: Is there any possible way to build a query that returns the data ordered in that way? That will really help me to build the application I need just by running through the results.I've been trying UNION, inner SELECTS and many other stuff without any good result. Just wondering if this is possible.
Returning A Field
It's difficult to explain what I'm trying to do, but this should help; name | new article | Ross | 1 Ross | 1 James | 1 Jeff | 1 Ross | 1 If this is the result of a query to select all the names/new article columns from a database where the new article column equals 1, how would I then return the name 'Ross' as the person with most new articles
Syntax Returning
i have a database on the follow format: city, status, status_action and for example i want to return peoples that have the status equal to 1 or 3 and status action equal to 1 or 3 from the city NY so i am using the follow query: city = 'NY' AND (status = 1 or status = 3) AND (status_action = 1 or status_action = 3) but it dont return me the right value, it always give for example status equal 1 of everyone and ignore status 3 i have tryed to use AND aswell and i go no return.
Returning Msg From Stored Procedures
I`m learning how to use SP in MySQL and have some doubts about returning msg from SP. I`ve done sth like this: SQL DELIMITER $$ DROP PROCEDURE IF EXISTS `site`.`addContact` $$CREATE DEFINER=`root`@`localhost` PROCEDURE `addContact`(IN c_type VARCHAR(30)) BEGIN DECLARE msg VARCHAR(100); IF TRIM(c_type)<>'' THEN IF (SELECT COUNT(*) FROM contacts WHERE `type`=c_type)=0 THEN INSERT INTO `contacts` (type) VALUES (c_type); SET msg='Successfuly added' ELSE SET msg='Already exists' END IF; ELSE SET msg='Type name must be provided.' END IF; SELECT msg; END $$ DELIMITER ; SP returns always one msg and it`s easy to use it later in app code i.e. PHP. I`d like to ask if this aproach (with select returning msg) is acceptible? Or maybe I should use OUTPUT variable inside SP to get such info?
Returning The Automatic ID On Insert ?
Is it possible to return the auto_increment ID on inserting a row ? It would save me alot of hassle so i don't have to do another query to get the ID + i'd have problems getting the ID of a row without the ID to say 'where pic_id = $id'. Then again i could just do 'where pic_created = $time AND user_id = $uid'
|
TRACKER
