MYSQL :: Using Variables From Php In Mysql?

Complicated Sql Using Variables - How To Throw In Exogenous Variables
The "avg" from the following SQL code gives me the number of weekdays between two dates.

Now I need to throw in holidays to see if there is a holiday between the two dates. I have holidays in a serialized array in a database field. In php I would unserialize it into a session variable.

How can I incorporate holidays into the calculation of result2?

SELECT
@days:=((UNIX_TIMESTAMP(dateEnded) - UNIX_TIMESTAMP(dateStarted)) / 86400) + 1,
@d:= MOD(@days,7),
@w:=DAYOFWEEK(dateEnded),
@a:=IF(((@d + @w) >= 7), 1,0),
@b:=IF(((@d + @w) >= 8), 1,0),
@c:=IF((@w = 0), 1,0),
@result2:=(FLOOR(@days / 7) * 5) + @d - @a - @b - @c,
ROUND(SUM(@result2)/COUNT(id), 1)as avg
...
GROUP BY month

Here's how I do it in php:

PHP

function sdsd(startDate, endDate) {
...
    //$arrHolidays = serialize(array(&#55614;&#57158;-07-04', &#55614;&#57158;-09-04', &#55614;&#57158;-11-10', &#55614;&#57158;-11-23', &#55614;&#57158;-11-24', &#55614;&#57158;-12-24', &#55614;&#57158;-12-25'));
    $arrHolidays = unserialize($arrHolidays);
    foreach ($arrHolidays as $a) {
        if ($startDate <= $a AND $endDate >= $a) {
            $result--;
        }
    }

View Replies ! View Related

Variables In MySQL
I am new to MySQL and am trying to acquire skills in MySQL without the
benefit of any formal training although I have been in IT for more
years than I care to admit (retired now). I have bought and read
several SQL and MySQL books and frequently reference the on-line MySQL
manual on http://www.mysql.com/doc/en/index.html. I have also browsed
this and related newsgroups with considerable benefit.

For all of my efforts I seem to have missed some basic points
regarding the use of variables in MySQL scripts.

I hope that the readers of this posting do not find the problem
described in the example code below too silly or trivial to comment
on.

Please advise me if I have chosen the wring newsgroup for this
posting.

As the example code shows, all I a trying to do is set a variable (v)
to the current contents of the cell in column i, row 1 of table t.

DROP TABLE IF EXISTS t;

CREATE TABLE t
(
i INT(2),
r int(2) NOT NULL auto_increment,
PRIMARY KEY (r)
);

INSERT t SET i = 1;

# This is the line that fails.
SET @v = SELECT i FROM t where r = 1;

The version of MySQL that I am using is 4.0.6-gamma-win. It throws
back the above code with a bland error message that does not indicate
what the problem is.

Am I wrong in thinking that variables can be used in this way in
MySQL?

Assuming that I eventually get variables to work I intend to set up a
while loop for going through the rows of a table referencing the cells
in several columns.

View Replies ! View Related

Variables In Mysql
I need to know how to define variables which will accept null values from the server .
I am new and if anybody know about variables.

View Replies ! View Related

Mysql Variables Not Set
Quote: OBJECT VARIABLE OR WITH BLOCK VARIABLE NOT SET
why some db is giving me this?

View Replies ! View Related

Mysql Variables.. Is This Possible?
I've got a table that stores positions of various elements along with other data, and when a user deletes one of these elements from their profile, I want to run an update query to reorganize the elements to fill the gaps left from the deleted rows.. If this sounds confusing, for example, say I have a table that looks like this after a few rows have been deleted:

id | title | pos_left
--------------------------
1 | abc | 100
2 | bcd | 200
3 | cde | 300
6 | fgh | 600
8 | hij | 800

now I want to update the rows so they look like this:

id | title | pos_left
--------------------------
1 | abc | 100
2 | bcd | 200
3 | cde | 300
6 | fgh | 400
8 | hij | 500

I know I can do this by running a query on each row with a for loop in PHP, but can I achieve this with a single UPDATE query, perhaps using a MySQL variable that increments on each updated row, and assign that incremented variable to the pos_left field in the next row?
I've tried the query below which doesn't work:

SET @num=0;
UPDATE admin_desktop SET pos_left = (@num = @num+100) WHERE admin_id = 3 ORDER BY title ASC

View Replies ! View Related

MySQL Variables, Modifying Microsoft SQL Sequence Insert For Use In Mysql
I have the following microsoft SQL statement that inserts 25 new
records in sequence. However I am trying to modify this to work in
MySQL.

I cant seem to get the variable decleration right. I tried using
DECLARE CurVal int but that generates a mysql error. How can I modify
the code below to work under mysql? Code:

View Replies ! View Related

Inserting Variables In MySQL Table!
Have you guys tried inserting variables in mySQL tables? Do I have to
use 'quote' as we had been doing to insert strings?

mysql> INSERT INTO occurrence (word_id,page_id) VALUES
($word_id,$page_id);
ERROR 1054: Unknown column '$word_id' in 'field list'

View Replies ! View Related

Setting MySQL Variables At Start Using My.ini
I am battling to set the default datetime_format in my.ini, if i add the line i want to change the setting, the service refuses to start!

The line i am adding is as follows:

set-variable = datetime_format="%y/%m/%d %H:%i:%s"

Am i doing something wrong here?

View Replies ! View Related

Using Variables In A MySQL Query - Problem PLEASE HELP
I have been having a problem sending a MySQL query with a php variable in it. This is my

$sql = MYSQL_QUERY('SELECT `messages`.`msg_no`, `messages`.`name_for`, `messages`.`name_from`, `messages`.`date_left`, `messages`.`message`'
. ' FROM messages'
. ' WHERE (`messages`.`name_for` = $query_for)'
. ' ORDER BY `messages`.`msg_no` DESC LIMIT 0, 30');
Furhter down the script I have:

while ($row = mysql_fetch_array($sql, MYSQL_NUM))
{
echo "<TR>";
echo "<TD width=ཉ%'>", $row[2], "</TD><TD width=ཌ%'>", $row[3], "</TD><TD width=ྃ%' >", $row[4], "</TD>";
echo "</TR>";
On the 5th line of the first piece of code I have the var $query_for, which has a string value. If I use this code in place of that line:

. ' WHERE (`messages`.`name_for` = Ben)'
It works perfectly but when using the variable there instead it says when I open the page:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

View Replies ! View Related

MySQL Values (for Variables) That Seem To Be To High
phpMyAdmin shows my some values for variables in the status are marked red. I assume, that these values might be to high.

Section Handler:
Handler_read_rnd 414 k
Handler_read_rnd_next 316 M

Temporary data
Created_tmp_disk_tables 3 k

Key cache
Key_reads 78 k

Joins
Select_full_join 3 k

Tables
Opened_tables 6 k
Table_locks_waited 5

Now I've got a lot of questions:

1) For Handler_read_rnd it says "You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly."
How could I find out which queries these are? And should I increase the Handler_read_rnd? How would I do this?

2) For Handler_read_rnd_next it says "Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have."
Same questions as in 1):
How could I find out which queries these are? And should I increase the Handler_read_rnd? How would I do this?

3) For Created_tmp_disk_tables it says "If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based."
The tmp_table_size value it 33,554,432 right now. How would I increase this?

4) Key_reads: "The number of physical reads of a key block from disk. If Key_reads is big, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests."
My key_buffer_size is 16,777,216, Key_read_requests is 3M.
So the cache miss should be 78 k / 3072k???
What am I supposed to do here?

5) Select_full_join: "The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables."
Is there an easy way to check the indexes of all tables?

6) Opened_tables: "The number of tables that have been opened. If opened tables is big, your table cache value is probably too small."
table cache is 64. What size should it be? How would I increase this?

7) Table_locks_waited: "The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication."
Is there a way to find out which queries / tables should be optimized?

View Replies ! View Related

Stored Proc In Mysql Varchar Variables
Heres a little test sp, I can't get it to use the @H variable in the
select , what am i doing wrong?, been messing for days now :(

The row caount always comes back as 0 , but if I just change the line
"SELECT count(*) INTO s from test where url=@'H';" to use url='tony'
for example or any other value it works as expected. Code:

View Replies ! View Related

Mysql Extended Server Status Variables
Here are 3 questions about extended-status and server parameters.

I have a server with 1G of Ram Dual Cpu running mysql 3.23.41.

1/ to know if table_cache need to be increased, what is the maximum rate of opened_table that must be seen. I read somewhere that 1 or 2 per sec was acceptable.

I have a server that most of the time is in the [0,2] opened table per sec but can go up
to 100 / sec at peak hour.

Current table cache is 64 planning to increase to 512.

Is there a way to find the optimal table_cache value?

2/ In the extended status what represents exactly open_files and open_tables. They seem
to be cummulative values but doing diffs at regular 5 min intervals shows that they can grow an decrease.

3/ Is there a way to find an optimal value for the number of needed file descriptors based on maximum number of connections, table_cache, number of tables in database etc...

Any referrence to server tuning based on extended-status will be helpfull.

View Replies ! View Related

How To Populate MySQL Database Using Variables In C Language
I want to insert values in Mysql database. The values are stored in a variable in my C program. How to refer the variable in the insert statement.

View Replies ! View Related

Storing Values Of Session Variables Into MySQL Database
I'm creating an online survey with about 100 questions. To make it more user-friendly, I have broken them down to "bite-size" pieces of 10 questions per page. On clicking the "next page" button, the responses get stored in session variables. At the end of the questionnaire, when they click the submit button, I'd like the session variables to be stored in the database and the session will be destroyed. Code:

View Replies ! View Related

Using Php Variables
how to include a PHP variable in a MySQL query- eg:

<?
$id = 2;
$query = mysql_query ('SELECT * FROM users WHERE id = $id');

?>

Basically this doesn't work- I 'm wondering what is the correct sytax to put the variable $id in the query

View Replies ! View Related

PHP Variables
How would I do something like this:
$number = 1;
$result = mysql_query( "SELECT * FROM blogs WHERE id = $number" );

?

View Replies ! View Related

Variables And Their Value
I just checked the MySQL status via PHPmyAdmin and found some Values marked red. The server has been up since about 12 hours.

1.)
Innodb_buffer_pool_pages_dirty has ha value of 3

2.)
Innodb_buffer_pool_reads has a value of 427
Now I have read somewhere, that one should increase the buffer size to solve this problem and that it should be set a little bit larger than the database.
Is there a easy way to find out how large all DBs using InnoDB are?
I saw that Plesk uses InnoDB for their databases. The are about 12 MB large. Should I set the innodb buffer pool sizefrom 8.388.608 to something over 12.000.000?

3.)
Handler_read_rnd has a value of 460k
It is said, that this value gets high if for example keys are not set correct.
How can I find out, which queries could be optimized?

4.)
Handler_read_rnd_next has a value of 435M
How can I find out, which queries could be optimized?
Would
Code:
mysqld --log-slow-queries --log-long-format start

5.)
Select_full_join has a value of 88
How can I find out, which queries could be optimized?

View Replies ! View Related

Declaring Variables V 4.0
I cant declare a varible in mySQL 4 i've tried:

declare @id as int
declare @id int
declare id int

any ideas?

View Replies ! View Related

Passing Variables
I'm a beginning with php/mysql sort of stuff. I have a form where a user selects an option from a combo box. I would like to use that as a portion of my mysql query, but I don't know how to pass the variable through to the query.
Once i'm in the query, I'm assuming it just becomes another "and" statement, but I'm not sure how I translate what the form information was to a sql statement.

View Replies ! View Related

Declaring Variables V 5.0
I am trying to do some procedural sql loaded from a file using a command like this:

Code:

mysql -h myhost -D mydb -u me -p < test_mysql_procedural1.sql

The contents of the file look like this:

Code:

CREATE FUNCTION myfunction() RETURNS INTEGER
BEGIN
DECLARE x INTEGER;
SELECT COUNT(*) INTO xt FROM my_table;
RETURN x;
END;

CALL myprogram();

and i keep getting syntax errors, saying this:

Code:

ERROR 1064 (42000) at line 1: 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 'INTEGER' at line 3

I am using mysql 5.0 on ubuntu hardy heron.

What gives? What's wrong with my DECLARE?

View Replies ! View Related

Setting Variables
I am using win and mysql 4.1, I found that I can't set variables in my.ini config file, msqld part, such as:
---
character_set_results=latin1
character_set_connection=latin1
---

error, it seems no such variables for setting,

but some variables such as:
---
slow_launch_time = 1
---
can be set in the config file.

View Replies ! View Related

Bind Variables
What are bind variables? And does mysql support them?

View Replies ! View Related

Wildcards With Variables
I am trying to run a part number against other fields that may contain the part number. For example part number "abc" needs to match other fields lets say for example "123abc44".
That field contains abc so I need a query that would search wildcard. Here is what I came up with but is not working:
Code:

SELECT * FROM Table1 G join Table2 A WHERE (G.%partnumbercolumn%= A.miscnumbercolumn)

View Replies ! View Related

CONCAT And Variables
I have the following statement:

------------------
SELECT CONCAT('hxxp://www.whatever.com/all.html?t=4&p=1&u=',@c) into @c;

set @stmt3:=CONCAT("INSERT INTO webs values ('",@c,"')");
prepare query from @stmt3;
execute query;
--------------

I think that I'm using correctly the CONCAT function but it doesn't work.

View Replies ! View Related

Using Variables In Scripts.
I am using SQLYog to do some MySQL development and I am finding that I can't just declare a variable and run a script. I've tried the same thing in MySQL Query Browser and it's the same thing.

For instance, i can't just type in the code below into the editor window and execute it - it comes back with a million errors. But if I wrap this code in a stored procedure, it works fine.

So my questions are as follows:

1. Does MySQL not support running adhoc scripts?
2. Is the problem SQLYog?
3. Is there another tool that can let me write adhoc queries?

DECLARE count_recs INT;

select count(*) into count_recs
from information_schema.key_column_usage
where constraint_schema = database()
and constraint_name = 'fk_address_city';

if count_recs > 0 then
select 'fk exists';
end if;

View Replies ! View Related

Setting Variables In My.cnf
how can I issue these client commands from inside the my.cnf file?

tep_db_query('set collation_connection=euckr_korean_ci', $link);
tep_db_query('set character_set_results=euckr', $link);
tep_db_query('set character_set_connection=euckr', $link);
tep_db_query('set character_set_client=euckr', $link);

View Replies ! View Related

SELECT INTO VARIABLES H
I have a table that has a date and a dollar amount.
For whatever the highest date is, I want to put that date and the summed up dollar amount for that date into varaibles.

I was doing this and it DOES work:

SELECT MAX(thedate) INTO @myDateVar FROM myTable;
SELECT SUM(amount) INTO @myAmntVar FROM myTable WHERE thedate=@myDateVar;

But then I thought I could simplify those two lines with the following one line:

SELECT thedate, SUM(amount) INTO @myDateVar,@myAmntVar FROM myTable GROUP BY thedate DESC LIMIT 1;

If I don't use the INTO part of the SELECT, the results on my screen are correct. But when I use the INTO part it only populates the @myDateVar.
I tried taking out the references to thedate and @myDateVar but I still can't get a vaule into the @myAmntVar with this new SELECT statement.
It seems no matter what I do the @myAmntVar always ends up NULL.

View Replies ! View Related

Variables Not Being Passed To PHP
Just successfully loaded MySQL and PHP, running under Windows XP (SP2).

Using old documentation - Site Point "database driven Website" book by Kevin Yank.

Example 1 works fine, but his just displays the current date.

Example 2 & 3 which require the variables defined in the html file to be passed to PHP doesn't work. The message in the PHP file displays everything EXCEPT the value of the variable.

Here are the contents of my files ;

xwelcome2.html
<a href="xwelcome2.php?FirstName=Kevin&LastName=Yank"> Hi, I'm Kevin Yanks! </a>

xwelcome2.php
<?php
echo("Welcome to our web site, $FirstName $LastName!");
?>

View Replies ! View Related

INSERTing With Variables?
I'm having a problem using INSERT with variables. These variables ($FirstName, $LastName, $Address, $City, $AreaCode, $PhoneNumber, and $Email) are created through an html form using POST, from the previous page. I've tested the contents of the variables and they're being sent through, so I know that's not the problem. However, when I try to insert these values into a new row like this:

$query = 'INSERT INTO `Customer` (`ID`, `FirstName`, `LastName`, `Address`, `City`, `AreaCode`, `PhoneNumber`, `Email`) VALUES ('', $FirstName, $LastName, $Address, $City, $AreaCode, $PhoneNumber, $Email)';

It doesn't add the new row. It works if I use coded values (like, replacing $FirstName with 'Bill'), but not with references to variables.

View Replies ! View Related

Computed Variables
is it better to have values stored in your database that are computed or to compute them each time. for instance you have 50 different records with say 10 fields
you always want to list every field in the record and 1 additional one that totals fields 1-10 is it better just to have that in the database or to total when you request the data each time?

View Replies ! View Related

Inserting With Variables
I want to insert something into a table by a form.
Could I do this?

<?php
// The configuration to connect to the database goes here
$name = $_POST['firstname'];
INSERT INTO Persons
VALUES ('$name')
?>

View Replies ! View Related

SQL Results As Variables
I hope someone here can help me figure this out. I was looking for a way to give each result from a SQL Query a variable. My reason for doing this is I'm trying to make each result an item in a drop down box to make searching the database easier for the users. Anyone have any ideas? I know how to sort & arrange the results, but how do you assign each one (no duplicates) a variable?

View Replies ! View Related

VALUES From Variables
im sure this is basic stuff, but i cant find anything on this yet...

$insert_query = "INSERT INTO `error`
( `company` , `location`, `employee`, `problem` ) VALUES
( 'x', 'y', 'z', 'a' ) ";

this is my code, but instead of x,y,z i need to put the value of my selected option. using php, i have them assigned to str1 -str4, how do i put those variables in as values?

View Replies ! View Related

Global Variables In Sql
can we use global variables in sql and what do we use them for ?

View Replies ! View Related

How Can I Change Variables?
I'd like to change these MySQL global variables:

character_set_database=utf8
character_set_server=utf8
collation_database=utf8_general_ci
collation_server=utf8_general_ci

How can I do this? I've tried to put these lines into my.cnf and restart server, but MySQL service doesn't start with these lines.

View Replies ! View Related

Changing My Variables
I'm really nubbin at this and just recently starting looking at MSQL database things.

The main question is can I change the Variables of my database?

Its a MySQL database...at I want to change

query_cache_limit = 1,048,576

to 62 million or whatever, also have a bunch of other values I want to change.

View Replies ! View Related

Passing Multiple Variables
I have a catalogue built and it passes a variable to the sample page that
shows the larger pic on the page.

But I want to pass a second variable to the page as well, can that be done.

This variable is the simple line of text that talks about the product.

This page does not access the database when loaded. It just uses the
variable passed from the page before....

View Replies ! View Related

Variables In Select Into Outfile
I wan to use a variable for the outfile file name in a select into clause. I am
doing something like:

set @fileName="/tmp/result.text"
SELECT col1, col2 INTO OUTFILE @fileName
FIELDS TERMINATED BY ','
FROM test_table;

the interpreter dies at the @fileName...
if I exchange @filename with "/tmp/result.text" it runs fine.

View Replies ! View Related

Passing User Variables
Is there any way to pass mysql user variables on the command line?
I'd like to do this to enable me to parameterised a mysql script.

I'd like to be able to use a command line such as:

mysql @uservar1=1 @uservar2=2 < parameterised_script.sql

So far the best I can find is to prepare a separate script
(parameters.sql) containing only the parameters:

SET @uservar1=1;
SET @uservar2=2;
....

and then pipe both scripts to mysql using cat.

cat parameters.sql parameterised_script.sql | mysql

Is there a neater way?

View Replies ! View Related