Subquery As An Array?
Let us say I have two tables:
- user: userid, username
- post: postid, userid
In a single query, given the "userid" value, I want to select "username" from the user table, and select all postid values from the post value where the userid="myuserid".
Something like...
SELECT
username,
(SELECT postid FROM post WHERE userid="myuserid") AS postids
FROM user
WHERE userid="myuserid"
I'm sure you can spot the problem--the subquery returns more than one row of data. Is there any way I can make "postids" be an array, or a comma-separated list, or something like that?
View Complete Forum Thread with Replies
Related Forum Messages:
Can A Subquery Show Up As A Column With Array Data?
Table 1: AlbumID AlbumName Table2: SongID SongName Table 3: KeywordID Keyword There is a one to many relationship from the Album and Song names to the keywords. But I'd like to do a SELECT where the keywords return as an array in one record. So the SELECT would return something like: AlbumName | SongName | Keywords ============================ 'The Game' | 'Another One Bites The Dust' | 'freddie','mercury', 'queen', 'song', 'music' If I join the 3 tables, it will generate multiple records for each keyword. I'm wondering if a subselect can return the array from the 3rd table?
View Replies !
Querying With Array On A Column That Contains An Array
One column of my table (that i didn't create) contains comma separated category IDs: parentlist -1 1,-1 -1 1,-1 3,1,-1 2,-1 For example, the red row is in category 3, which is in category 1 which is a parent category. I also have an array of values (2,3,4) called $catchoice. I want to find where my $catchoice and the parentlist values overlap. I know how to do this if the parentlist was only one value but since it contains multiple values I am at a lost. Do I need to combine a MATCH() function with the IN() function?
View Replies !
SUBQUERY Vs DEPENDENT SUBQUERY
I have a table called `accounts`, that contains columns: account_name VARCHAR (128) account_no VARCHAR (16) account_hash VARCHAR (32) balance DECIMAL(10,2) There is an INDEX on account_hash and there's currently 128 rows in the table. I have a table called `activity` that contains columns:....
View Replies !
Subquery Or Correlated Subquery Help
I need to develop a sql that uses the results from the first Query to find data in the second Query. Then the results of the second query to find the final results of the third Query. I’m also wondering if I should try to just link all these tables together instead of Subqueries or Correlated Query. First Query select ACCOUNT_ID, ACCOUNT_TYPE_C, PAT_ID from PAT_ACCT_CVG where ACCOUNT_TYPE_C in (120103,120104,120101) Second Query SELECT PAT_CVG_FILE_ORDER.PAT_ID, PAT_CVG_FILE_ORDER.LINE, COVERAGE.COVERAGE_ID, COVERAGE.CVG_EFF_DT, COVERAGE.CVG_TERM_DT FROMPAT_CVG_FILE_ORDER LEFT OUTER JOIN COVERAGE ONCOVERAGE.COVERAGE_ID = PAT_CVG_FILE_ORDER.COVERAGE_ID Where coverage.payor_id = ?' Third Query select TRAN.ORIG_SERVICE_DATE TRAN.TRAN_TYPE, TRAN.INSURANCE_AMOUNT from Tran where TRAN.TRAN_TYPE = 1 and TRAN.INSURANCE_AMOUNT > 0 and TRAN.proc_ID in 1008,1009 (now I need to compare the dates on this query to make sure that the TRAN.ORIG_SERVICE_DATE is within the COVERAGE.CVG_EFF_DT, COVERAGE.CVG_TERM_DT ( dates of the second query)
View Replies !
My SQL Array And PHP
I am using a select statement to obtain a table from mysql database. I want to store that table(with two columns Quesid, Numans) in an array which is sorted according to Numans. My table looks like this: Quesid Numans 3 5 2 3 4 2 1 2 5 1 Now I want to store first field of column Numans which is 5 into a variable, divide it by two and then parse through all the rows of the array and print that Quesid whcih is closest to 5/2. Once printed again go through the array with same condition and again print the next closest one.
View Replies !
Array
see i have two tables.technology and category.In technology i have the fields id(auto_increment primary) and name. In category i have id(auto_increment primary),categoryname and categoryid. In the first table i insert the name and the value of the id is auto_increment. now am going to insert the categoryname in the category table by using technology.id=category.categoryid.(the name in the technology table ). how can i use the array .
View Replies !
Where = Array(
Ok i was reading a book and i saw something i had never seen before. THey were using something like where `id` = array(1,2) Or atleast something similar,. UNfortunately i forgot the correct syntax and cant remember which book it was. Whats the correct syntax?
View Replies !
Array Parameter
since mysql's stored procedure does not accept an array as a parameter, is it a good practice to input a string delimited parameter eg. call stored_procedure(param1, param2, 'value1:value2:value3');
View Replies !
Creating An Array
I want to select a bunch of email addresses from a mysql table and format them to use with a php mail() function. I can select them from the database, but how do I format them so there's a comma and space after every one?
View Replies !
Use An Array Column
I am building a photo album webpage and am not sure exactly how I should organize the database. Here's where Im at so far... I have one table called 'images' which contains the columns 'id','albumID', 'url', 'title', and 'description'. Then another table called 'albums' which contains the columns 'id' and 'name'. The part where I am having the trouble is the 'images.albumID' column. Some photos can be apart of mutliple albums, so should the 'albumID' column be an array type?
View Replies !
When To Use An Array Column?
I am building a photo album webpage and am not sure exactly how I should organize the database. Here's where Im at so far... I have one table called 'images' which contains the columns 'id','albumID', 'url', 'title', and 'description'. Then another table called 'albums' which contains the columns 'id' and 'name'. The part where I am having the trouble is the 'images.albumID' column. Some photos can be apart of mutliple albums, so should the 'albumID' column be an array type?
View Replies !
Array Or List
I need to make a list of all the products a client has ever ordered or that they are interested in so that later I can see which clients might be interested in a specific product. What's the best way for me to do this? Probably with JOINS, but I'm not sure how. Doesn't seem right to create a new field for eachof 200+ products.
View Replies !
Store An Array
I'm using PHP to acces my MySQL database; Now, I would like to store an array in the database. I was very suprised to see that there is no array-type field (samen with boolean? why didnt they make a boolean field?!). Anyone an idea how I can save and recover my array from the db
View Replies !
Array Fields
I want to create a field that will contain multiple values (an array of integers for instance). Here is the example: ID name numbers ===================== 1 John {2,3,4} 2 George {} 3 Martin {3,6,12,45,77} How do I define this table? I want column "numbers" to be a set of individual numbers, and not a varchar field nor I want to create another table to hold those numbers. Is that possible? CREATE TABLE foo( ID INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY, name VARCHAR(10) NOT NULL, numbers ????????? );
View Replies !
A Record Value As An Array
I have a table where in some records field = '1234,2345' So, I use this field as an array of several values Now, I would like to select all records where one of the values in the array are equal to some string. When, in the above example, I do the following: SELECT field1, field2 FROM [table] WHERE 1234 IN (field2) This works!!! But, when I do the following: SELECT field1, field2 FROM [table] WHERE 2345 IN (field2) I get nothing Is there somehow to solve this issue?
View Replies !
Query Within An Array?
Is it possible to store an array of values in a database column and then do a query for a single value within that array? for example row 1: 1,2,3,4 row 2: 5,6,7,8 row 3: 1,3,5,7 row 4: 2,4,6 return all rows containing a 7 return = 2 and 3
View Replies !
Array Duplication
For the checkbox part, I have used two tables one a list of the skills (pny_services) and one a lookup table (pny_services_lookup) which holds reference keys to the pny_users table and pny_services table to match users with their checked skills. Whilst the routine seems to find everything ok when displaying the values onscreen the users are duplicated the number by the number of skills that they have with one skill per user display. here is the code in its current ....
View Replies !
WHERE IN With A Two Dimensional Array
there are two tables, both have a and b cloumns. i need data from table 1 but the conditions are in table 2. the amateur 'logic' would want me to use this: SELECT * FROM table1 WHERE a,b IN (SELECT a,b FROM table 2 WHERE c=5)) wont work, but i need to handle 'a' and 'b' together as alone they aren't unique (together they are), and my solution was adding a unique column but i'm still wondering if the above example is possible without this additional column
View Replies !
Using Database Value In An Array
I'm developing a site for a gaming club that uses a ranking system. To make it easier on myself and other administrators, I've made the ranks field of my main database table into numeric values and defined an array that includes the full title. How do I convert the numeric value in the database to the matching array value?
View Replies !
Fetch Array
$result=mysql_query("select tags from blog ORDER BY tags ASC"); while($row=mysql_fetch_array($result)){ $allthetags = "$row[tags]"; echo "$allthetags "; //will pull out all the tags from all the rows, tags are seperated by a space } $tags = "$allthetags"; //this would pull all the tags for only one row I'm trying to pull out all the tags from all the rows instead of just one row.
View Replies !
Array Update
I have imported a flat-file database into mysql. Some of the tables have arrays with values such as "a||b||c" or "a||g||s" where a, b, c, g and s are keys for the real values, i.e. apple, boy, cat, girl and snake. I want to update my database to replace the coded array "a||b||c" to "apple||boy||cat". I am not sure how to go about doing this. I believe I may have to use the explode, implode and case functions, but don't know where to start.
View Replies !
Getting All Of An Array From Mysql
I've had no formal training in PHP/MySQL, just what I pick up from my books. So, as many others have done, I am creating a simple front page where it shows news with a date greater than or equal to today's. The problem is, I always lose one news item (somehow due to my arrays), but I don't know enough about the language to understand why. So, I've got my connection stuff, then this: $sql = "SELECT content FROM `news` WHERE `date` >= '2007-01-28' ORDER BY date"; $rs = mysql_query($sql, $conn); $foo = mysql_fetch_array($rs); if($foo) { $row = $foo; } Note that I still have a permanent date, because I've been working on it over a while and I don't want to have to change the dates in my db to be in the future. Then, down later, I've got: while ( $row = mysql_fetch_array($rs) ) { echo ($row["content"] . "<br>"); } As I said, I always lose the 1st result, but am not sure quite why.
View Replies !
Array And Sort
I have products that belong to certain categories. I want to array the product (mysql_fetch_array), and ORDER BY the Category they belong to. However I dont want the category to array each time. I want CATEGORY ONE | CATEGORY 2 product one | product one product two | product two The code i am using is -- $query ="SELECT category.cat_id, category.catname, inventory.cat_id, inventory.product, FROM category INNER JOIN inventory ON category.cat_id = inventory.cat_id ORDER BY catname ASC"; ................. while($row = mysql_fetch_array($result)){ echo "<tr><td>"; echo $row['catname']; echo "</td><td>"; echo $row['product']; ETC How can i array the products with out the Category, but have category as a heading??? where the arrayed products will go under.
View Replies !
Too Many Columns: Array?
I'm trying to convert a system based on text files to MySQL. It currently uses the following two files for user registration: A text file that holds name, email, password, etc. A text file that holds the email and approximately 100 other values. These values each range from -100 to 100. I want each text file to be a separate table, but I don't know how to best implement the second table. Is it bad to have so many columns? How would I type it out without writing CREATE TABLE vals (..., val40 tinyint, val41 tinyint, ...)? Is there anything like an array in MySQL?
View Replies !
Select Where Array?
Is it possible to select multiple rows from a table with a query that includes an array variable? like if $array was an array of id numbers Select * from table WHERE insert_id=$array
View Replies !
Ordering An Array
I have a php array that I need to figure out how to order differently. Here is the code that I am using: Code: $prod_id = array(); $result = mysql_query("SELECT * FROM categories2products WHERE cat_id='$cate_id'"); while ($row = mysql_fetch_array ($result)) { $prod_id[] = $row['prod_id']; } foreach($prod_id as $value) { $result = mysql_query("SELECT * FROM products WHERE product_id='$value' AND display='yes' ORDER BY product_title ASC, product_id ASC"); while ($row = mysql_fetch_assoc ($result)) { The second query is how I want to have it ordered, but for some reason it doesn't do anything with that order part. How would I get it to order that way?
View Replies !
Array Type Fields
I'm quite new to MySQL and quite impressed by its feature set. I've also been looking at Interbase and it has a feature that allows a multidimensional array to be stored in a single field. Does MySQL also have this feature? I was thinking about using MySQLs GIS data types but it only supports a two dimensional point, where as I need a three dimensional point to be stored.
View Replies !
Searching Within An Array In A Table
Is it possible to search within a one-dimensional array in MySQL? For example, say I had the follow table: ID | Day | Hours ---+-----+------ 1 | 1 | (1, 2, 3) 2 | 2 | (2, 3) 3 | 3 | (1, 3, 4) 4 | 4 | (1, 4) Would it be possible to select all days that have a "2" in the Hours field? Like: SELECT Day FROM table WHERE "2" IN Hours; or something to that extent. Possible?
View Replies !
Store Numerical Array
I would like to be able to store numerical array in MySQL. I am really looking for the solution to store it in the one field only. For example, I have a matrix, which I want to store in the table, but I don't want to create one column per matrix element - too many columns or matrix size could change easily. Should I convert all elements from numerical type into string and create one long string with element separators or it is better to serialize the whole array and write it as a byte array? What other options are available?I know Oracle allows to store the array directly in the DB. How MySQL approaches this problem?
View Replies !
Getting SUM Of A Table Row Built With Array
I have a query that gets placed into a php array and echos into a table: Code: SELECT location_code , cars_qty , boats_qty , motorcycles_qty FROM auto_list WHERE state='CA' ORDER BY location_code I also want the bottom row of the table to display the table's totals: Code: SELECT sum(cars_qty) AS sum_cars , sum(boats_qty) AS sum_boats , sum(motorcycles_qty) AS sum_motorcycles FROM auto_list WHERE state='CA'
View Replies !
Select Array Items
How can I select an array of items in mysql. I would like to use a SELECT query. Instead of Select * from equipment WHERE itemid = 111 OR itemid=112 OR itemid=113. Can I just create an array of itemid's and use that array in the mysql statement. Is this possible??
View Replies !
Total Of A Multidimensional Array
I have a multidimensional array... while($row = mysql_fetch_array($query)) { $array[$row['type']] = $row; } Later on I display the value for type 'session' in a table... <? echo $array['session']['count']; ?> Question... I am now expanding the query that will include more values in the array (i.e. month to date rather than just a particular date)... How can I display the total value for this array (not the count)?
View Replies !
Counting Instances In An Array
I have no experience of designing, databases or using script or code but I now find I have to work with an existing database. I have a field in a table called outstanding objectives. It contains data such as Y,12,Y,Y,1,3,Y (Its is an array writen as text to the field by Flash) another field in the same row indicates the number of items in this array, in this case the entry would say 7. Id like to know how to count the number of Y's in the array, then express this number as a percentage of the value in the second field e.g. 4/7 and then write the percentage value into a new field.
View Replies !
Array In Database Field?
I am making a database of work performed on equipment. I have a field "parts_replaced" that will have a integer for each part replaced. Can i make a array type variable that has multiple part numbers? If not suggestions on another way to do it? thanks... i am ID = int(11) serial_number = int(11) machine_type = text model_name = text date_purchased = datetime work_performed = text date_work_performed = date parts_replaced = int(11)
View Replies !
Search Array Column
I'm trying to search an array in the database. One of my fields has something like this: 1,2,22,224 So if I do: WHERE Value LIKE '%2%' It will return: 2,22,224 If I do: WHERE Value = '2' It will return nothing... How can I just get '2' ?
View Replies !
How To Create An Array Of Tables.
how to create an array of tables in MySql ? I want to creat an array of tables: 900 sets of (names varchar(500), address varchar(500)) where each set indexes to a different location. (i do not want to build seperate tables, just one array of tables) Also, if in the future, the allocated space is not enough, how can i extend the limit in the same array of tables without losing data? thanks
View Replies !
Create An Array From Sql Query
I need help with a mysql query, I am adding some java code to a page which requires a list in array format: array("value1", "value2", "etc") I need to be able to return the results from a sql query and convert them into an array. The query looks something like: SELECT value1, value2 from some_table ORDER BY value1 ASC" I only need one of the values returned from the query in the array.
View Replies !
Mysql Data Into An Array
I would like to read IDs from a database into an array and then pass that array to a javascript function. If I'm thinking correctly, this should be possible as php is server side and javascript client side. Anyways, I've got the following query: mysql_query("SELECT id FROM hatchery WHERE parent='0' ORDER BY id") and want to pass the selected ids into an array to be used by a javascript function.
View Replies !
Selecting Rows Not In Array?
I'm trying to pull a single random field/row out of a mysql database, based on records that the user has not viewed already. I can either store an array of IDs in a session var or a cookie, but then how do I get mysql to exclude those IDs when selecting a random number? I'd like to 'SELECT ID FROM Table WHERE !in_array($array) ORDER BY RAND() LIMIT 1' but of course the "in_array" php code won't work in a mysql query. Is there a way to do this in mysql only, rather than having to iterate through the array and build a query with possibly hundreds of elements in the array: WHERE ID!=# AND ID!=# AND ID!=#,
View Replies !
Date Difference Sum & Array
I'm trying to work out the interest on a whole bunch of records and then add them all up - hopefully in one query. This would be a standard query if I wanted to get a list of all charges and the interest rate $cID = $_GET['cID']; SELECT date_charge, amount, interest_rate FROM charges LEFT JOIN claims ON cID = '$cID' WHERE chcID='$cID' $interest_rate_f = $interest_rate / 36500; But how would I go about getting that one query to perform the following calculation? amount * ( curdate('Y-m-d') - date_charge ) * interest_rate_f amount = 27.50 curdate = 2006-10-13 date_charge = 2000-08-25 interest_rate_f = 0.000219178 $interest = 27.50 * ( 2240 ) * 0.000219178 = 13.50 And then add all the $interest up in the query to output it as $interest_rate_sum PHP Version 4.2.3
View Replies !
Storing Array From Multiselect
If you had to store an array from a multiselect in MySQL, what would be the cleanest way of going about it? Considering that the array elements are ids that I will use later to grab records from another table, should I make a connector table for this information? One hack would be to store the ids in a comma-delim list and grab the corresponding records using a separate SELECT, using IN(). Yuck. What is standard practice?
View Replies !
Query Against An Array In A BLOB?
I've inherited a problem that I'm struggling a bit with. My CMS sends email notifications (using a cron) to users when pages or comments are updated. The current problem is the system creates a new row in the notification table *every* time a change is made. So if 10 changes are made to a page between cron runs, the user gets 10 emails (when a single email would suffice). I was going to add an if statement to this function that checks to see if the $content['nid'] or $content['cid'] we are planning to insert has already been added to the table. If so, skip the insert. If not, perform the insert. Here's the function (This is MySQL via an abstraction layer. Hence the syntax): function subscriptions_hold( $content, $ptype , $op, $pid ) { $strqry = 'INSERT INTO {subscriptions_holding} ( content, ptype, op, pid ) VALUES ('%s', '%s', '%s', %d)' db_query($strqry, serialize($content), $ptype, $op, $pid); } Here's the table structure: CREATE TABLE `subscriptions_holding` ( `rid` int(11) NOT NULL auto_increment COMMENT 'Unique row ID', `content` blob NOT NULL COMMENT 'The node array', `ptype` tinytext NOT NULL COMMENT 'post type - node or comment', `op` tinytext NOT NULL COMMENT 'The operation on the node', `pid` int(11) NOT NULL default Ɔ' COMMENT 'The ID of the poster', `skip_date_first` datetime default NULL COMMENT 'First Date skipped', `skip_date_last` datetime default NULL COMMENT 'Last Date skipped', PRIMARY KEY (`rid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
View Replies !
Result Row To Be Multidimensional Array
Say I have a table "Employees" and a table "telephones" with 2 columns: the employee ID and the telephone number. Each employee can have more than one tel number so the "telephones" table can have multiple rows with the ID of a single employee. What I want is to run one query, where each row in the result will have the details of the employee and a nested array with the telephone numbers. Something like: [name] => John [surname] => Jones [telephone] => Array ( [0] => 342134123423 [1] => 324123412344 ) [position] => Manager . . .
View Replies !
Grown Defects In A RAID Array
I've notice that when grown defects (bad blocks on the disk caused by usage over time) that my dedicated mysql server is adversely effected. Since the action of Grown defects does not flush the table with a write lock-that the block marked as bad will effect prior to that block becoming a grown defect-what is another way for mysql to recover from this situation? I'm currently using EXT3 does this same effect happen with Reiser-Fs / UFS / Veritas?
View Replies !
|
TRACKER
