Assigning Query Results To A Variable
I would like to Execute this Select Statement...
SELECT MAX(VisitorID) FROM Visitors.
Is there any way to store the result directly into a VB variable??
lngVisitorID = ???
View Complete Forum Thread with Replies
Related Forum Messages:
Simple Sql Question: Using A Query Result As A Query Variable
EDIT: it works now, I had an error in my code, not my method. I have a very simple question. I have 2 tables: 'users' and 'posts' with the following structure: users: id, username, email_address posts: id, user_id, post_title, post_text in a my own mind's mysql, I would like to: SELECT posts.id, posts.user_id, posts.post_title, posts.post_text users.username FROM users, posts WHERE posts.user_id = users.id I usually do one query for the post data, and then, based on the use_id record, do another of the users table, but today, I'm being forced to do them in one swoop.
View Replies !
Variable Within The Query
I have: SELECT userid, count(*) AS matches FROM userinfo_responses WHERE username = 'vikdhu' (have skinned the query to take out irrelevant...) Now I'd like to use 'matches' in the WHERE clause too - like SELECT userid, count(*) AS matches FROM userinfo_responses WHERE username = 'vikdhu' AND matches > 20 but this doesn't work.. is there a way out?
View Replies !
Syntax For LIKE Query With Variable
$badwords=array("ass","babbi","penis"); foreach ($badwords as $badword) { //Delete query $delete_sql = "DELETE FROM jos_facileforms_subrecords WHERE name LIKE '%'.$badword.'%' "; // execute $delete_sql query and get result $filter_sql_result = mysql_query($delete_sql,$connection) or die(mysql_error()); echo $filter_sql_result; }
View Replies !
Can I Use A Variable In A Mysql Query?
this works: $sql = 'UPDATE `personal` SET `interval` = '24' WHERE `ID` = 66 LIMIT 1'; but this does not (I get a parse error): $variable=24; //php $sql = 'UPDATE `personal` SET `interval` = $variable WHERE `ID` = 66 LIMIT 1'; my question is: why would this not work? Am I not allowed to use a variable in my query? Perhaps my syntax is wrong.
View Replies !
MySQL Variable Query
SET @tcslot = "my_column"; SELECT @tcslot FROM my_table WHERE id = 1; The above code returns: Quote: @tcslot my_column It literally returns the value of the variable I set. Is there any way to get it to parse the variable and then select the column contained in it? It's been driving me nuts for 3 days,
View Replies !
Passing Variable Into Mysql Query
I can't seem to get a value to pass from a url into a mysql query... Here is my code: The url is...page.php?record=$row[recid] $record = $_GET['record']; $query = "UPDATE databaseName SET data='$data' WHERE recid = $record "; $result = @mysql_query ($query); When I print $record I get the resulting number from the recid but when I put it into the query I get nothing. When I print $query I get nothing for $record. I'm very confused...
View Replies !
Trying To 'parse' A Variable Within A MySQL Query
I'm trying to do an INSERT query via a PHP script. Basically, I want to use a variable within the WHERE clause, but i'm not having success. PHP $SQL = "INSERT INTO users (uPassword) VALUES ('$newp2') WHERE (uName='$userid')"; The above results in the following error: "check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (uName='test')' at line 3"(where 'test' is the variable stored in $userid). 1. Is it possible? If so how? What is the syntax? 2. If not, any other way I can constrain a WHERE clause by using a variable?
View Replies !
Passing Variable Into A Table Query
Newbie here again. I'm using MySQL, version 4.1.21-standard and php. This is probably one of those "boy this guy is an idiot" type questions, but I have to ask it anyway My db has several tables of yearly statistics. The user has a drop-down menu with choices for which year to view data. This choice produces a variable labeled $year. The tables are labeled like so: 2006_results 2005_results 2004_results etc. and fields such as 2006_results.winnings 2006_results.status 2006_results.sponsor Can a query take the previous variable and use it to request the specific table, such as: If $year = 2006, can the query structure uses $year_results.winnings to get 2006_results.winnings? Oh, I almost forgot. If I force the query I'm using for a hardcoded year, such as 2005_results.winnings instead of using $year, the query works. I just can't get it to use the variable.
View Replies !
Access User Defined Variable In Mysql Query
using loop and getting string in user defined variable like uvariable. now executing the statement in java file resultset=statement.executeQuery("select abc from bcd where T=""+uvariable+"""); t and bcd are column.getting error message "check syntax".
View Replies !
Resetting User Variable Causes Variable To Not Hold Decimals
I am currently working on a query that makes use of user variables. I notice one small issue (bug?) depending on whether or not I reset a user variable with the "SET" command or don't before executing the query. When I do reset the user variables, some of my variables will not hold decimal points (7.56734234 gets turned into and stored as 7). However when I run the query afterwards without resetting the user variables, the correct value (with the decimal) is stored. Example with query: ..
View Replies !
Assigning Index
I think there is a simple answer to this, but this is an issue I haven't had to explore as of yet for some reason.I am in the process of importing a batch of new records into the database and I need to assign a unique value in one of the fields that will serve as the primary key. The field where this value will appear is obviously already the primary key field and the records in the database already have a unique value found there. For the life of me, I can't figure out how to assign a unique value for the primary key of these newly imported records that will not duplicate the primary key found in the existing records.
View Replies !
Assigning Status As Reserved
------- tbl_dates.id tbl_dates.week_date -------- tbl_cottages.id tbl_cottages.cottage_name -------- tbl_booking.id tbl_booking.cottage_name tbl_booking.book_date ------- tble_dates.week_date contain dates in the format 'January 1 - January 7'. the tble cottages contain a list of properties the tble_booking contains entries which I input when a cottage becomes booked so I know when the cottage is unavailable on what dates. What I want to be able to do is cross reference the top 2 tables against tble_booking. So if the tble_booking contains ID = 1 Cottagename: Bluebells Week_Date: January 1 - January 7 If some one clicks on the webpage to check the availabilty for the week 'January 1 - January 7' the cottage will be marked 'reserved' and the rest will be marked 'available'. I have tried doing it with a LEFT OUTER JOIN but it marks them all as reserved? SELECT tbl_cottages.id, tbl_cottages.cottage_name, tbl_bookings.id, tbl_bookings.book_date, tbl_bookings.book_cottage, FROM tbl_cottages LEFT OUTER JOIN tbl_bookings ON tbl_bookings.book_date = tbl_cottages.cottage_name WHERE tbl_bookings.book_date = '#URL.calendar_date# If you need any more info please ask!!
View Replies !
Unknown Column (assigning Using AS)
SELECT u.user_id, u.user_name, ( SELECT COUNT(friend_from) FROM friends WHERE friend_from = u.user_id ) AS friends_added, ( SELECT COUNT(user_referered) FROM users WHERE user_referered = u.user_id ) AS friends_referered, friends_added + (friends_referered * 10) AS points FROM users u Quote: #1054 - Unknown column 'friends_added' in 'field list'
View Replies !
Assigning Unique ID To Distinct Values
I have a table `books` with booktitle and authorname columns. In my applicatoin booktitle has unique values but authorname doesn't. ie an author can have many books but not the other way around. I need to add more author info in the database. So I need a new `authors` table with authorname as a column (along with address, email, phone etc) and an authorid primary key. Then I need to replace books.authorname column with a column containing the corresponding authorids How can this be done in sql?
View Replies !
Assigning User Privileges Error
I created a user 'cody' % and localhost and try to assign privileges to his database 'cal_cody' For some reason it don't work, posting this weird thing in forums didn't help either. so I tried: mysql> GRANT ALL PRIVILEGES ON cal_cody.* TO 'cody'@% IDENTIFIED BY 'access'; Query OK, 0 rows affected (0.00 sec) also tried: mysql> GRANT USAGE ON cal_cody.* TO 'cody'@'%' IDENTIFIED BY 'access' Query OK, 0 rows affected (0.00 sec) then tried the same for 'localhost' then even flashed the privileges and even reloaded the mysql service doesn't do jack Is there something wrong with my command, or is there a way to do this through phpmyadmin?
View Replies !
Assigning Auto_Increment Value To Antother Column
I've got a table with an auto_increment integer in it. What I want is basically that when I do an INSERT to create a new row, the value that that auto_increment column has been assigned should also be assigned to another integer column. I could of course do this using a second query, but would prefer to wrap it up in just the one. Anything I have tried so far has failed, assigning 0 to the second column. For example, if a is the auto incrememnt column and b is the column to copy the value into: INSERT INTO table (b) VALUES (a) b is 0 - presumably because it's assigning b before it's assigned the auto_incrememnt value to a. I've also tried INSERT INTO table (b) VALUES (LAST_INSERT_ID()) INSERT INTO table (a,b) VALUES (0,LAST_INSERT_ID())
View Replies !
Assigning Userids To Existing Table
I have a table of existing users in a database. I want to add a "userid" column to the database that automatically assigns an ID to each user when new accounts are added to the system. I know I would do something like this if I had the brains to have added the column at the time of creating the table: ALTER TABLE writers ADD ( userid int PRIMARY KEY auto_increment ); However, the table is now already active with users, and I want userids assigned to existing accounts on the system.
View Replies !
Help In Assigning Unique Keys To Table Entries
Allow me to share some design problem that I am facing. I have the following table definition: CREATE TABLE `image_info` ( `Image_ID` int unsigned not null, `Title` varchar(255) default '' not null, PRIMARY KEY (`Image_ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; This simple table holds information about images. Each image has a title. Note that the primary key was NOT defined as auto_increment (explanation below) Also note that the table engine was defined as MyISAM (a system requirement) The range of image IDs that can added into the table is split into 2: 1) IDs 1,000,000 and above are reserved for images that come from an external program which defines the IDs. 2) IDs 1 - 999,999 are reserved for images uploaded by users in runtime in my site. This means: When I add an image from my application (1), I already know in advance what Image ID I need to use and I can safely add it using: insert into image_info (Image_ID, Title) values (1000010, 'I am a photo from an external program'); I know that image ID 1000010 is reserved just for that photo and so there won't be any duplicates. When a user uploads a photo to my site (2), that photo needs to be stored in the table with the next available ID which is < 1,000,000. I use the following query: select max(Image_ID) from image_info where Image_ID < 1000000; I then add 1 to the value I am getting and this is the ID I will use for the image uploaded by the user. The problem occurs when too many users are uploading photos at the same time. This is a problem of atomicity. 2 Users may be assigned the same "free" image IDs and then one of the queries will fail when adding the record to the table because of duplicate IDs. I cannot change the order of the ID ranges (making 1 - 999,999 for my external program and 1,000,000 and above for images uploaded by users), as this is a system requirement. If I didn't have this requirement, I could have set auto_increment on the image_id field. I am looking for a solution to my problem. Can anyone think of one? Maybe using an additional table which uses auto_increment to assign IDs? I am developing in PHP, so maybe I need an application-level solution on top of MySQL
View Replies !
Save Query Results
In other words, how do I build a new table from the results of a query, so I can access pieces of this data later without performing the same query (a very heavy one) over and over again?
View Replies !
Filter Results Of One Query
I'm looking for the syntax that would allow me to filter one querys results using another query i.e. QUERYA = SELECT * FROM table WHERE id < 100 QUERYB = SELECT * FROM QUERYA WHERE id <> 49 I understand that I can do the above example in one query - I'm just using that to simplify matters.
View Replies !
Order Query Results
Regarding a dummy set of data below, ID Status 1 Open 2 Closed 3 Expired 4 Closed 5 Open 6 Cancelled 7 Expired 8 Cancelled I want to view the results but order them according to the status. i.e. i want to view in order of Open, Closed, Expired, Cancelled I can only think of a long drawn out way by which i would create the four seperate queries.
View Replies !
Query Results By Pages
I would like to display query results in pages of say 20 results each. e.g. a table with two columns, serial# and descrition, with a few thousand entries. What would be the SQL query to find a specific serial number, and display all 20 results within the "page" that row happens to be in? e.g say serial#=3211 is in row 64, display rows 60->79.
View Replies !
Formatting Query Results?
I have a shell script (sh) that queries a MySQL database and then formats the results to an html page. I realize shell is probably not the best choice for this but it's all I know. My problem is that I need to do a mysql query for every field in every record in order to put it into the html page. This is obviously very slow. This is an example of what I'm doing now: ProductLine=`mysql -u www -s support <<EOF SELECT ProductLine FROM Documents WHERE ID = '$ID'; EOF` DocType=`mysql -u www -s support <<EOF SELECT DocType FROM Documents WHERE ID = '$ID'; EOF` DocName=`mysql -u www -s support <<EOF SELECT DocName FROM Documents WHERE ID = '$ID'; EOF` FileName=`mysql -u www -s support <<EOF SELECT FileName FROM Documents WHERE ID = '$ID'; EOF` Like I said, I am doing this for every field in every record. It works but it is VERY slow. I thought if there was a way to tell the query to insert a special charater between each field then I could use awk to pick out the fields and only do one query per record... something like this: SELECT ProductLine, DocType, DocName, FileName FROM Documents WHERE ID = '$ID'; I don't know how to take that output and put it into my shell variables.
View Replies !
Saving Query Results
Is there any way to save the results of a query without the quote marks? When i try to import the text file that I saved the query to, I have to go thru and delete all of the quote marks.
View Replies !
Query Giving Me Results I Don't Want.
query: SELECT sales_reps.sr_id, sales_reps.order_id, sales_reps.name, sales_reps.job_number, UNIX_TIMESTAMP( shop_orders.date ) AS date, sales_reps.stage, sales_reps.status, pi.fname AS pfname, pi.lname AS plname, sales_reps.date_to_shop, users.builder, users.division, shop_assignments.editor_id, shopper.fname AS sfname, shopper.lname AS slname, ei.shop_result, ei.good_tape FROM sales_reps LEFT JOIN shop_orders ON shop_orders.order_id = sales_reps.order_id LEFT JOIN users ON users.user_id = shop_orders.builder_id LEFT JOIN shop_assignments ON shop_assignments.sr_id = sales_reps.sr_id LEFT JOIN users AS shopper ON shopper.user_id = shop_assignments.shopper_id LEFT JOIN users AS pi ON pi.user_id = shop_assignments.pi_id LEFT JOIN exit_interviews AS ei ON ei.shop_id = shop_assignments.shop_id WHERE sales_reps.status = 'Rejected' AND ( ei.shop_result != 'Trip x1' OR ei.shop_result != 'Trip x2' OR ei.shop_result != 'Non-Reg Trip x1' OR ei.shop_result != 'Non-Reg Trip x2' OR ei.shop_result != 'Office Closed' ) ORDER BY users.builder, users.division, shop_orders.date DESC , sales_reps.stage ASC It's not filtering out the records with the shop_result field matching what I've listed.
View Replies !
How To Get Distinct Hour(s) In Query Results?
I need to write a query that will count the number of records that have been written for each hour of the day. This must be done entirely in SQL, and I am not an SQL expert. I know how to break the hour out of a timestamp using the hour() function, but I'm unclear on how to get the breakdown like I described above.
View Replies !
Eliminating Duplicate Results From Query
I'm trying to dynamically build pull down menus based on the contents of a column (in order to build a search query). But the column may have the same data entered multiple times. This results in a pull down that has the same option listed several times. I wonder if I can eliminate duplicates from that array? So that it only appears once in the array.
View Replies !
Update Query Results To Another Table
So I'm learning, bear with me, this is probably pretty easy(but I am a bit perplexed) but I'm writing a php page for a cron. I am selecting a user name from a table basically selecting all row entries in that table with a certain username, and adding them together. $query=mysql_query(SELECT UserName, SUM(AcctOutputOctets) FROM radacct GROUP BY UserName) The output is this(when I run a query in mysql) UserName..............SUM(AcctOutputOctets) test..................... 345566 Basically i'm adding up all the users entries and totaling their Usage. Now the question. I want to write that to another table named mtotacct. format like this. Username..............TotalOctets. I'm just a little uncertain how to properly set this up to pass the results from the query to the UPDATE statement.
View Replies !
Showing Query Results In HTML
In mysql I can find the minimum data from a particular column by doing as follows: SELECT MIN(Freq_Min) FROM datatable; and mysql will display the results. Now I want to do the same thing but I want to do it in html, so I wrote the following code. When I open up the webpage stats.php it says that it couldn't execute the query. Can someone help get me on track? .....
View Replies !
Use Query-results As Delayed Insert?
Can I use the results of one query like: -> SELECT user_id FROM tbl_customers And use the results to fill the VALUES-statement in another table like: -> INSERT INTO tbl_postal ('user-id') VALUES (<previous query results>) If so, could someone complete my second SQL-statement, since I'm staring at my CRT for over 2 evenings and I cannot get it to work.
View Replies !
Query Is Not Returning Desired Results.
My query is not returning desired results. I want to structure my query to return the Post Subject, Post Text, and Poster. Instead, it is returning the correct Post Subject, correct Post Text, but the Poster is just repeating the username from the '$_POST[username]' variable. How can I alter the query to return the correct name? the query PHP <?php$conn = mysql_connect( $domain, $user, $password ) or die("Err:Conn");$rs = mysql_select_db($db, $conn) or die("Err:Db");$sql = "SELECT `table_posts_text`.`post_subject`, `table_posts_text`.`post_text`, `table_users`.`username` FROM table_categories, table_forums, table_topics, table_posts, table_posts_text, table_users WHERE username='$_POST[student]' ANDcat_title='$_POST[category]' AND`table_categories`.`cat_id`=`table_forums`.`cat_id` AND `table_forums`.`forum_id`=`table_topics`.`forum_id` AND `table_topics`.`topic_id`=`table_posts`.`topic_id` AND `table_posts`.`post_id`=`table_posts_text`.`post_id`AND `table_topics`.`topic_poster`=`table_users`.`user_id`AND`table_posts`.`poster_id`=`table_users`.`user_id`ORDER BY `table_topics`.`topic_id`, `table_posts`.`post_id`"; $rs=mysql_query($sql,$conn);while($row=mysql_fetch_array($rs)){ echo($row['post_subject'] . $row['post_text'] . $row['username']);}?> There are six tables involved: 1. table_categories (cat_id medint, cat_title varchar) 2. table_topics (topic_id medint, forum_id smallint topic_title varchar topic_poster medint) 3. table_forums (forum_id smallint, cat_id medint, forum_name varchar) 4. table_posts (post_id medint, topic_id medint, forum_id smallint, poster_id medint) 5. table_posts_text (post_id medint, post_subject varchar, post_text text) 6. table_users (user_id medint, username varchar)
View Replies !
Reversing Query Results (not The Same As ORDER BY)
I am trying to reverse the order in which the results of my query are given. I am tweaking a gallery. I need to get the previous 3 pics based on the pic I am looking at. The following code is a simplified version of what I have. The problem is, when I echo the results, the thumbnails are displayed in DESC order (i need them to display in ASC order) but if I order the query using ASC, the query gets the wrong images. SELECT pic_id WHERE pic_id < $current_pic_id ORDER BY pic_id DESC LIMIT 3 I need the results to show like this: oldest pic | older pic | old pic | current pic | new pic | newer pic | newest pic I've got the new pics sorted out but the old pics are causing a little problem... So basically, is there a way that I can reverse the results of my query?
View Replies !
|
TRACKER
