PHP :: MySQL Query And Arrays

Populating Arrays From MySQL Query
I have the following code: http://pastebin.com/746601

The field 'material' in 'is_material' contains multiple values for each
record in 'is_details'. Because of this I have used
'is_material_lookup' as a reference lookup table containing the
'style_code' and 'material_code' which refer to their full details in
the respective tables.

Currently I have got the script outputting all the details and one
material then in the next block of data, repeating the details with a
different material. What I would like to achieve is having 1 block of
data with a list of all materials in that, instead of the repeat, but
sadly I can't know exactly how to do it.

Using Arrays With Mysql Query Results
im currently in the process of making a piece of code that will firstly take all the data from a table in mysql and then assign it to an array. This data will consist of several different rows of data each with different infomation for each field in the table.

i want to be able to use an array in a loop so that depending on which number of the loop is on will change the infomation that is assigned to a certain array. Code:

Help Needed Using Arrays In Query
I am try to use an array in a MySQL query using the in function. This requires a "," between each array entry but not at the end of the array. I can get a "," in between but I am left with one at the end. How can I get rid of the last ","?

Query About Empty POST Arrays
I started programming in PHP recently and have a query about empty
$_POST arrays. I can see two scenarios when this could happen.

1. When some tries to directly load the page to which data is being
posted (for example, opening www.foo.com/xyz.php directly when a form
action is xyz.php)
2. When the user clicks the submit button without entering anything.

I am aware that Javascript validation can take care of the second case,
but it's unreliable. What is the accepted way of handling the above
situations on the server side?
Do I just reload the calling page if the array is empty?

Arrays, Query Results And Select Box
I want/need to pre-select a drop-down box with a value if it's present in both a lookup table and user account table. After wrestling with this for a few hours I give up.

i know this can't be this complicated, but apparently i'm missing something, so if anyone can suggest a way to go about this i'd be a little more sane. The idea is to have the user value already selected then when they change the value i'll update their account.

Disable Passing Arrays In Query String?
Is there any way to stop PHP from turning a query string like this: ?var[] into an array when it is read by $_GET['var']? I have quite a few input validations that are easily screwed up by this strange (and apparently undocumented???) feature...

PHP Update Query Mysql: Query Succussful?
I can't seem to successfully test an update query. Below is a piece of code that updates a statistics table. If row today doesn;t exist, a new day must be created. But this doesn't work...

thank you for your reply.

$today= date("Y-m-d");

$qq="update totals_r set hits_r=(hits_r+1), lang_$lang=(lang_$lang+1), cat_$cat=(cat_$cat+1), rating_$rating=(rating_$rating+1), bussite_$bussite=(bussite_$bussite+1) where date='$today'";

$result_6 = mysql_query ("$qq");

if (!$result_6){ /// IF !ROW_TODAY ==> CREATE NEW DAY

$qq_2="insert into totals_r (hits_r, lang_$lang, cat_$cat, rating_$rating, bussite_$bussite, date) values(Ƈ',Ƈ',Ƈ',Ƈ',Ƈ','$today')";

$result_6_2 = mysql_query ("$qq_2");

if (!$result_6_2){
$err_msg_sql_6= mysql_error();
$err_loc_6="$PHP_SELF"." // Query6: update totals_r";
}
}

Arrays In MySQL
Can I store an array into an SQL database and then take it back out and it would still be an array?

Arrays Into Mysql
The mysterious message: Couldn't add data to the CATLINK table:Unknown column 'Manufacturing' in 'field list' PHP Code:

Arrays And MySQL Querys
I can't get an $array[key] to be used as a select query on a mySQL database.

I have a 'parts list' which displays on a page (works fine) and is a form.
At the end of each row is a input type='text' (quantity) cell with a default
0

The quantity can be altered.
At the bottom of the table (form) is a submit to basket Button

echo "<form action='basket.php' method='POST'>....

Arrays And While Mysql Functions
How can I get this array to work? I am trying to add data to an array for every row of data? This is a sellection of queries that I need to parse into an array so a function can use the arra to draw a graph. I am new to arrays how can I get this to work? Code:

Arrays Working With MySQL
How can I grab a certain field of a Multi-Dimensional Array using the ID. I have set for that Array? I just want to be able to create a simple function to do pretty much what this function does, but for an array. Code:

PHP Saying Error In Mysql Syntax, But Written My Mysql Query Browser!
I have a basic db that I access with MySQL query browser. Everything
seems fine to me but I am using this db as part of a php shopping
basket and when I try to add an item I get:

Notice: Query failed: You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near '>function.extract]: First argument should be an
array in functions.inc.php on line 31
Notice: Undefined variable: price in functions.inc.php on line 36
Notice: Undefined variable: price in functions.inc.php on line 39
Notice: Undefined variable: total in unctions.inc.php on line 39

I'm assuming the last three are caused by this problem as price should
be passed to the cart, and total is worked out using it. However
although I know mySQL code it was the MySQL query browser that
actually generated the code and I cannot see a way to view or debug
the code.

The db has one table in it which is made up of id, name, subname,
desc, and price.

The code in the php file that is being referred to is:

Passing PHP Arrays To Mysql Best Solution
I am relatively new to PHP and until now I have only needed to use relatively simple php/mysql solutions.

I am working on a problem where I need to store CD information (Artist Name, Album Name, Track Names, Catalog Number)

I am working on a solution with two tables "album" and "tracks" and I am using the catalog number as a key to link the two.

The problem I am having is coming up with the best way to populate the tracks table as there are several tracks for each release. I am assuming that an array would be the correct way and maybe a dynamic form to input the tracks where you decide the number you need to add and this then creates a form with "n" input boxes?

Insert Multiple Arrays Into Mysql
I am having problems writing the code to generate my SQL INSERT query. I have 3 arrays and one static variable ($cat_number):

$track_name[]
$music_link[]
$track_number[]
$cat_number

Should I be using a foreach command. I know how to insert multiple values but its generating the query that i'm not sure about.

INSERT INTO table (track_name, music_link, cat_number, track_number)
VALUES
(var1,var2,var3,var4),
(var5,var6,var7,var8),
(var9,var10,var11,var12);

I don't want to store the actual array in the dataabase, I am just using an array to catch the data.

Multiple Arrays And Foreach To Put Into A Mysql Db?
I created 3 arrays with data and i want to insert them i a mysql db. PHP Code:

Multiple Selections, Arrays, And MySQL
OK this place has been great with explaining things that are way over my head, now I could use a boost to get my brain working again. I have a form with a drop down menu with multiple selections. When the form is submitted it shows up in the DB field as "array", as well as the page where I how the form's info. I think I have to set up an array, and loop(?) it, or do I need to implode the array? And how and where would I put these commands on this incredible messy form page. Would someone mind helping me get started?

Here's the form: (please don't make fun of me, I used to a Dreamweaver extension to create the form using PHP, before I decided to try and write this stuff myself. I am re-doing the form but this actually works for now, so I am using it. Code:

Sorting Arrays Retrieved From A MySQL Db
I am trying to make a news system. I have done it, but the news are listed with the last on at the bottom. This makes it kind of troublesome to read the latest news when you enter the site and there are a lot of news items, cause you would have to scroll down.
PHP Code:

MySQL Field Type For Arrays
What type of field type are arrays stuffed into? VARCHAR ? TEXT?

How To Insert Arrays Into Mysql Database
This is my order.php file. Now I try to make a new php-file for processing the ordered items on this page, into another table "ordered_products" in the database. I want to INPUT following values into the table "ordered_products":
'order_num', 'order_item' and 'order_ant'

I know how to insert rows in a table using the INSERT query, but I don't know how to do it while looping the arrays. I have to fetch the arrays from this page, and insert them row by row in the "ordered_products" table. Code:

Merging 2 Mysql Resultant Arrays
im currently executing sql queries on two seperate mysql servers. i take back each of these results into individual tables to display them.

What i wanted to do was perform a merge of the two arrays, so that i could order the results by my "TimeStamp" field.

When i try to perform a basic " $mergedresult = array_merge($result, $isaresult); " i get the following.

"
Warning: array_merge() [function.array-merge]: Argument #1 is not an array in C:Program Filesxampplitehtdocsseatpub.php on line 164

Warning: array_merge() [function.array-merge]: Argument #2 is not an array in C:Program Filesxampplitehtdocsseatpub.php on line 164 "

Im rather new to this and so havent delt with arrays before, but im under the impression my results from the sql queries, are Assoc arrays. can these be merged ? do i need to use an alternate php function ?

Pesky Checkbox Arrays And A MySQL Song Database
I'm not one who likes to ask for help, so for the last three days my brain has been turning to mush while I try to create a DJ Request Song Wish List. Anyone who can help me out will be saving me from severe insanity. :)

My goal is to:

1. Display search results for song title, artist and genre. (accomplished)
2. Allow the user to add songs to their Wish List. This is done by displaying checkboxes with the corresponding value equal to the songID. (working on it)
3. Allow the user to update their wishlist by adding/deleting songs.

So far I have a form that retrieves the song info from a database in a repeating region. In Dreamweaver:

<input name="songID[]" type="checkbox" id="songID[]" value="<?php echo $row_RecordsetSearchResults['Title'];?> by <?php echo $row_RecordsetSearchResults['Artist'];?>">

This puts the corresponding songID number into a checkbox value, which in turn is submitted by form POST action to the following page as an array.

To process this array I have:

if(isset($_POST['songID']))
{
$songID = $_POST['songID'];

print 'Song IDs:'
// process items
$n = count($songID);
for($i = 0; $i <= $n; $i++)
{
//See if the values are being passed from POST
echo $songID[$i];
echo ' '
}
$songsInsert = '*'.implode('*',$_POST['songID']).' '
$insertSQL = sprintf("INSERT INTO wishlist (Username, Title) VALUES (%s, %s)",
GetSQLValueString($MM_Username, "text"),
GetSQLValueString($songsInsert, "text"));

mysql_select_db($database_ToneDeaf, $ToneDeaf);

$Result1 = mysql_query($insertSQL, $ToneDeaf) or die(mysql_error());
}

The problem is, I'm using implode. It does store the songs, but only as one long block of text. So if the user picks "New York by Frank Sinatra" and "Hot In Here by Nelly" it is stored as "*New York by Frank Sinatra*Hot In Here by Nelly*" Obviously this gives me no options to make this actually readable, let alone allow the user to remove one of those songs from their list (both would be have to be removed.)

Also, referencing only the songID number as stored in the music database doesn't seem a good option, since the song database will be changing frequently and song data may be overwritten. Not sure if there's a viable workaround there.

Originally I wanted to store Title, Artist, Genre under seperate columns so I could link them to something like "See more songs from this Artist." However the more I read about arrays and multidimensional arrays, I realize I'm not ready to go there yet. (I just started learning PHP two weeks ago.)

Comparing Values Of 2 Arrays Combined With Mysql Field Names
I am trying to create an edithistory table that has a text column into which I will put a string that can be read using strtok later to revert changes if necessary. I want to build this function so that it's dynamic enough to be applied to every table so that I don't have to write one for each table that might be updated.

When a row is added to any table (a new entry), it retrieves the next available editid # from the edithistory table and assigns it to that item in the "edits" column for its row in its own table. Every edit made is a new entry in the edithistory table (entryid auto_increment primary key), and the editid is a reference back to the object being edited. Code:

Query MySql Using PHP
I've created my first search form and it seems everything is working pretty well. However, since I am very novice in scripting in general, I'm having one problem that I can't seem to unearth an answer to. Perhaps someone could easily offer the answer:

my query asks to select a 2 char state code or a 5 digit zip code in order to obtain results desired. In testing, if I enter a state code, it brings up only the state requested. However, when I enter a zipcode, I get the wrong results. I'm sure its the "LIKE" that is throwing this off. Here is the query portion of the script:

$result = mysql_query ("SELECT * FROM fish_dealers
WHERE Zip = ´$Zip%´

OR State LIKE ´$State%´
");

Can anyone give me a clue?

Mysql Query
I have wrote a Select statement, that checks if the user and password and returns a value, however can i add something to the statement that would allow me to get the userID from the table users, so that i can write the value to a cookie, is it possible. PHP Code:

MySQL Query
I want to have a field where I store a list of what categories that
particular row falls under, I don't know which format to do or how I
would query it.

For example, row1 might belong to categories 1, 3 & 7 - I'd want to be
able to query all rows belonging to 3 and get row1.

AND And OR In MySQL Query
I've build a query from a php form where people van select more results for a cell wich can only contain one value Here's my query:

SELECT * FROM person WHERE color_eye = ('brown') OR color_eye = ('black') AND color_hair = ('Blonde')

Everything after the "OR" will be ingnored. So how can I tell MySQL I want all the brown and blacked eye?? Without the AND it works fine, but in real the query is way longer with multiple AND's.

Mysql Query
I have a code that installs some tables into a mysql database. Heres the code, but it keeps coming up with the error "Install failed (ID 1)" I have tried tons of things, even looking at other installers and mimicking them, but nothing. PHP Code:

Php/mysql Query
I want to display the users name on the pages when they have logged into my site. i have done it one way that is to take what they type in the login form and post it on the page and that works fine. only one problem and its more of a preference than a problem but the way i did it takes exactly what they type say "username" and posts that so if they type "UsErNaMe" it will post it like that. i want to show the name exactly as they make it when signing up. im guessing i have to query the data base... mine being MYsql. PHP Code:

Mysql Query
How can I get rownum using MySQL queries ,
In Oracle one can you in this way SELECT rownum as srno, fname FROM
tablename
suppose If there are 45 records and if i use while for the

srno should display 1, 2, 3, ... 45

query like SELECT rownum as srno, fname FROM tablename

I dont want to use php counter, i want it from query

Mysql Query
I'm running a sports pool and I'm trying to display stats from the
results of the picks.

The table is 'results' and the fields are 'pick_number', 'game_number',
'user_id', 'points' and 'date'

There is one record for each users' pick. 0 points are given for a loss
and 1-4 points are given for a win. I've already created a query that
gives me the top players by overall points but I'd like to expand that
to include the number of points won just this week, overall winning %
and winning % this week. Can I do this in one query and then display in
an HTML table?

$query = "SELECT user_id, SUM(points) AS points FROM results GROUP BY
user_id ORDER BY points DESC";

Mysql Query
1) i have a database divinelive_messages table with a sessid column
2) i need to find out rows from the database, which only show that sessid in there once.

Mysql Query With * And +
im trying to return all results that come from or went to certain extensions on our phone logging system but i don't want to return any that have a src or dst that contain a * or a +. This is the where statement i have that is not working Code:

Getting MySQL Query
I took some info out of my database. I want to display an error page if their are no rows in the database. How would I go about doing that. I have this:

$query = I execute my query here. Taken it out so you don't connect to my database Muahahahhaah

$row = mysql_fetch_row($query)

if ($row == '')
{
echo 'Their is nothing in the database!';
}else{
echo 'Yes, the database does home info in it.';}

What would I put to make this work?

MySQL Query
Whats wrong with this query:

SELECT from, subject, message, time FROM user_messages WHERE username='$username' ORDER BY timestamp DESC

I get this error:
QuoteYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from, subject, message, time FROM user_messages WHERE username='Drezard' ORDER B' at line 1

MySql Query
I'm having some difficulty formulating query that will allow me to retrieve the details of a specific user in the database. The user's information is stored in one table called Users and another called UserProfile. Both tables are linked by the UserID field.

My current query returns a Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource and my knowledge of Mysql is not great(Still learnng)

Here is my code:

Need Help With Basic Php Mysql Query
$z = "goodBMW"; the word goodBMW is no where to be found in in the table times. but it will echo every single row thats in that table and it doesend sort between Group 1 and Group 2 where the hell is my query wrong.

Problematic MySQL Query
Would anyone be able to help me with the following MySQL problem. I am
trying to extract all of the users who are not on vacation for a given
date. I am using a NOT IN statement, however, I need to have a more
advanced query than a simple one. What I need to do is as follows
SELECT users.user_id,...

MySQL Query Quandry
I have a simple need to go into a 4 column table and based on the content of the first 3 print the content of the 4th. In other words if column1 = a, column2 = b, and column3 = c then print "dog" from column4. This will be the action for a short form that allows the user to make 3 choices that will determine a link. I have this from another script that I'm trying to modify:

mysql_select_db('search_links');
$query = "select * from links ";
$result = mysql_query($query);
$num_results = mysql_num_rows($result);
echo 'Number of books found: '.$num_results.''
for ($i=0; $i <$num_results; $i++)
{
$row = mysql_fetch_array($result);
echo ''.($i+1).'. Title: '
echo htmlspecialchars(stripslashes($row['type_prop']));
echo ' Author: '
echo stripslashes($row['area_code']);
echo ' ISBN: '
echo stripslashes($row['price_range']);
echo ' Price: '
echo stripslashes($row['saved_link']);
echo ''
}

The above query prints the entire table and I would like a query that limits to the form choices indicated above.

PHP: MySQL UPDATE Query
I need to have a page where the user can update his info and/or delete his record. I haven't tested the DELETE query yet.

When I open the page it shows all the info stored in the database. When I change something and hit the update button I get the message that the record was successfully updated, but nothing really hapens.

If you want to see what's happening go to URL , click on the 'directory' link
The user ID is 'scooby', and password 'dooby'. If it does not work, try the following combination: user ID: 'sunny', password: 'trout' . Then click on the 'edit profile' link and try to change something and see what happens. I am attaching a php file of the 'edit profile' page, the one I am having trouble with so you can see the entire code.

Getting Variable From Mysql Query
what's the best way to set a variable = to a result from a mysql query?

Advanced Mysql Query
There are currently two queries in one of my scripts. One is:

SELECT * FROM foo

the other is:

SELECT var FROM bar WHERE something = 'somethingelse'

Is is possible to use a join statement and combine these two into one query? My reason is that I'm trying to speed up the script, and hopefully combining the queries will do that.

Mysql Date Query
I need to check if one script was run later than one hour ago - I had a problem with cron and want to put in a safety code.

On successful running of the script I remember the date in db
CREATE TABLE log(id INT NOT NULL auto_increment, ttime DATETIME, other_info TEXT, PRIMARY KEY(id));

The sql query that I use now is:
SELECT HOUR(CURDATE() - ttime) as h FROM log ORDER BY h LIMIT 1;

However I have a problem - it returns sometimes value NULL (which is always first).

If I use the following syntax:
SELECT HOUR(CURDATE() - ttime) as h FROM log WHERE h IS NOT NULL ORDER BY h LIMIT 1;

I get the error: Unknown column `h` in `where clause`. What can I do?

Mysql Query - Select Any
I have the following sql statemant to search a mysql database that gets
if values from a form with combo box's in.

SELECT * FROM hottubs, manufacturers WHERE manufacturers.manid =
hottubs.manid AND hottubs.type = '%s' AND hottubs.dimlength <= '%s' AND
hottubs.dimwidth <= '%s' AND hottubs.dimhight <= '%s' AND
hottubs.seatsto <= '%s' AND hottubs.shape = '%s' ORDER BY
$thesearchtype_search.

Everything works fine except I want to add a select "any" from the
shape combo box. I really need a way of cutting out the last " AND
hottubs.shape = '%s' " if the $_GET['shape'] = 'any'
Will this work using a variable as shown below??

IF ($_GET['shape'] != 'any' )
{
$shape = AND hottubs.shape = '%s'
}

SELECT * FROM hottubs, manufacturers WHERE manufacturers.manid =
hottubs.manid AND hottubs.type = '%s' AND hottubs.dimlength <= '%s' AND
hottubs.dimwidth <= '%s' AND hottubs.dimhight <= '%s' AND
hottubs.seatsto <= '%s' $shape ORDER BY $thesearchtype_search

I have only been doing php about a month so go gentle!

MySQL Query On The Same Page.
here it is: i have a table with lets say 2 fields, first_name and last_name. When user enters index.php he gets database records sort by first_name (ORDER BY first_name). Now i ave made two links above those records. They are: sortb by firts name and sort by last_name.

My question is, what code should be written for the link sort by last name if i wanna show the same records already displayed but sorted by last_name???

And how do i make that if user has clicked sort by last name that thislink is disabled (not underlined, just bold text) and if he clicks on sort by first name that this link becomes text.

MySQL Query With PHP Variables
My Query is like this I have two dates inter in by the user and set to $x(lower) and $z(higher). I want to pull all entries that are between these 2 dates.

This does not work (Why?):

$result = @mysql_query("SELECT id, maincourse, veggie, veggie2, fruit, bread, dessert, drink, DATE_FORMAT(day, '%a %b, %D') as date_string FROM $fromwhere WHERE day<=$x AND day<=$z");

note: "WHERE day>$x" does work.

Query Inconsistency (MySQL Vs PHP)
I have the following query:

select DATE_FORMAT(accountingdate,"%c") as month,sum(totalprice -
freightcost - insurancecost - vat2 - vat3 - vat4) as totalprice from
invoice where cancelled=0 and deliveryagentid=0 and (employeeid=0 or
employeeid=37 or employeeid=53 or employeeid=50) and
DATE_FORMAT(accountingdate,"%Y")="2005" group by month order by month;

Giving the following result in MySQL (correctly):
+--------+--------------+
| month | totalprice |
+--------+--------------+
| 1 | 540274351.00 |
| 10 | 119601657.00 |
| 11 | 118712994.00 |
| 12 | 109391926.00 |
| 2 | 112606305.00 |
| 3 | 99175084.00 |
| 4 | 101754796.00 |
| 5 | 120049120.00 |
| 6 | 138227787.00 |
| 7 | 166565653.00 |
| 8 | 136475650.00 |
| 9 | 133242379.00 |
+--------+--------------+

When I run the same query in PHP (on a web site, I get):

4 538068.00
4 538068.00
4 538068.00
4 538068.00
4 538068.00
4 538068.00
4 538068.00
4 538068.00
4 538068.00
4 538068.00
4 538068.00
4 538068.00

Mysql Search Query
Is there a way to search for a word in any of the fields of a row using simple sql for my sql?

I have a rather complex search function on my site and right now, I dump all the text of all fields (that are text fields) in a
special field. The search function then searches this "keyword" field.

Is there a smarter way of doing this.

To recoup

I have an mysql database with about 500 records, each containing over 20 text fields (address, name, etc.... etc....). I want the
user to be able to search for a particular word (e.g. fishing or rock climbing) in all records and all fields and I would like Mysql
to return the IDs of the fields that match.

Along the lines of

SELECT ID FROM TABLE WHERE * LIKE "%test%";

Date/MySQL Query
I am making an event calendar that allows users to add events. An event can possibly span multiple days so I made two fields for start date and end date. This all works fine.

I want to be able to grab the data out of the database through a query and display it on the webpage in order of date. So I'm ordering by the start date. However if the event is 3 days long I want to show the event on the page 3 times, with all other events in between also show. I have not been able to figure out the logic on how to get the event to show up 3 times with other events in between them?

Sample Data:
Event 1 Dec 31 2002
Event 2 Dec 31 2002
Event 1 Jan 01 2002