MYSQL :: Trying To Avoid Using Query Inside While Loop

Preventing A Count Query Inside A Loop How?
I have an idea in my head and im pretty sure its possible but I don't have enough mysql knowledge to apply. Basically, I have this:

SELECT user_id FROM ml_group_subscriptions WHERE user_group = $id
then in php I initialise a loop go over the results:
PHP

while ($row = mysql_fetch_array($get_users))
                {
                    $this_user = $row['user_id'];

and then an insert query:

INSERT INTO ml_subscriptions (`user_id`, `campaign_id`) VALUES ($this_user, $add_to_campaign)
while this is all dandy, I don't want duplicate data. My only current method would be
PHP

$get_users = mysql_query("SELECT user_id FROM ml_group_subscriptions WHERE user_group = $id");
                while ($row = mysql_fetch_array($get_users))
                {
                    $this_user = $row['user_id'];
$check = mysql_result(mysql_query("SELECT COUNT(user_id) FROM ml_subscriptions WHERE user_id = $this_user"),0);
if ($check == 0) {
                    $insert_users = mysql_query("INSERT INTO ml_subscriptions (`user_id`, `campaign_id`) VALUES ($this_user, $add_to_campaign)");
}
                }

as you can see this is putting a query inside a loop which could be potentially huge. Can I not adapt the first SELECT query to only accept data that is not about to be duplicated?

Why While Loop Want Query More Than Once?
Can someone explain why $displayResults will not query twice?

Scripts Objective:

to take a car's VIN retrieve the car info and display in nice rows

Issue with Script:

The while loop displays the first car with information but on the
second loop it will does not run the query for $displayResults all
though the $queryDisplayResults does change the car's Vehicle ID
Number

What am I not doing right? ......

How Do I Do A AND With A Bunch Of OR After WHERE Inside A Query?
Ok, I have something like the following:

SELECT dog WHERE ago = 1 AND (fur = brown or fur = black or fur = white)

can I do that? I need to find dog MUST be 1 year old, but their fur can be either brown or black or white.

Loop Mysql Query
I have 2 mysql tables. lets say One table has id and name, the other has id_pic, foto, description and name_id

I have a query that join both tables and make a comparation so i can have the name and the fotos and descriptions. I did this because each person can have multiple fotos. So one table has name of the persons and the other has pictures.

My query is: $query_rs_fotos = "SELECT * FROM table 1, table 2 WHERE table 1.id = table 2.name_id";

Now i want to have the result of this query looped but i don't know how to loop it and separate the data. Lets say i have this data:

Accumulating Values Inside A Query
I only have experience of simple queries, and this one is a bit beyond me. I've done some research, and I think it might be possible to do within a single query, but I'm not sure.

I have a single table. It contains columns (among others): date, number. I want to produce a table of DATE, and new_number_count, where 'new_number_count' is the number of numbers on day with date DATE that do not have rows on previous DATEs.

For example, if this is my table:

SQL Loop - Direct In Query Builder
I am wondering how to perform multiple inserts within a GUI of mySQL. I guess what I want is a for loop just like a php for loop

for (start; condition; variable++)
...insert into mysql (theID, someValue1, someValue2) values (variable, 1, 2)...
...send insert to server...
next

Is there a way to do it using only sql or do I have to use a php script?

Calling A Php Funtion Inside A Mysql Query
hi there, im trying to call a php function that i have created inside a query and im getting an error.when i remove the funtion statment the query is successful. i was wondering is it possible to call a php function inside a query, if so how do i go about doing so.

this is my code:

How To Apply Php Or User-defined Functions Inside A Query
Instead of applying a php or user-defined function to data after fetching, I want to apply functions inside queries, not sure if this is possible.

This is how it's done with MySQL function:

SELECT
FROM_UNIXTIME(table_articles.article_pubdate, %M %d, %Y)
AS pub_date FROM...
Is it possible to replace FROM_UNIXTIME() with the php function date() ? so it's something like

SELECT
".date("table_articles.article_pubdate", 'F d, Y')."
AS pub_date FROM...
The above is just an example, actually I want to apply another user-defined function, which formats text of the field article_content.

How Can I Pass A String Parameter To A Query Inside A Procedure?
I want create a procedure to drop the table named as the input parameter....

Continous For Loop Query Generates Connection Terminated Unexpectedly?
I have an application where I have one central server and many other local servers. So from each one of the local or the central server I can actually move a number serials. So then in the same time I have cental table which keeps a record of each serial and where it is active now I mean in which of the servers. So the problem comes when I move a big number (around 600) of serial say from server A to server B. Then in server B I will receive all the transfered serials and run a for loop to check each of the serial agains the central table which is kept in the central server. The problem is when serial number is small is ok but when it gets big I get the error connection terminated unexpectedly? I know is due to heave query so is there any solution to it ? Below is a snippet of my code....

Query Inside Query?
I would like to know if its practical to Insert a Select statement inside a previous select statements (array).

With that said, the 'inner' select having a WHERE statement thats dependant upon the array results...

Avoid Adding More Than Once
im trying to do what i think is a fairly easy query but im having some problems.

basically i want to only include a certain row of data in the results if it has previously met a certain criteria. however if it has met the criteria more than once i still only want it to return once.

Avoid Self-joins
I have a table that has values of variables for certain entities. The
columns of interest are targetID, variableID, and valueID. A row (1, 5,
9) means that target number 1 has a value of 9 for variable 5. Being
denormalized, target number one will have many possible rows in this
table, one for each variable for which it has a value.

My problem occurs when I want to find out what targets match a certain
set of variable values. For instance, I want to find out what targets
have a value of 9 for variable 5 and a value of 25 for variable 10. I'm
thinking that this can be a simple self-join:

SELECT mya.targetID from mytable as mya
LEFT JOIN mytable as myb
ON mya.targetID=myb.targetID
WHERE (mya.variableID=5 AND mya.valueID=9)
AND (myb.variableID=10 AND myb.valueID=25)

Does this make sense so far? The problem is that this doesn't scale.
When I have more than 31 variables and I need to evaluate them all,
MySQL breaks: I can't do more than 31 joins.

My design calls for perhaps 80-100 variables, so even 64-bit
architecture with a limit of 64 joins won't get me there. This is NOT
an architecture or platform issue - I need a design and a data
structure that will scale to lots of variables.

I need another data structure that won't get me stuck on too many
joins.

How To Avoid Race Condition?
How do I lock a table for one of my insert (followed by a read) queries on a table such that other simultaneous insert/read queries on that table are put off until the first one is complete? I am trying to avoid a potential race condition.

Count(*) To Avoid Duplicates
This is for (2) seperate sites that share the same login table (same username/passwords will access both sites).

Users may register at either site, if they choose, then they should login at the other site to "complete" the last phase of registration at the other site to insert remaining needed values.

Problem is with duplicates. When some users won't login, as they should, "complete" the process at the other site. They may bypass and register anew like anyone else at the site for the first time. (Even though there is a pre-registration page at both sites -- asking if users have already registered at the other site prior to arriving at the second -- directing a login to complete the process.)

Reason this is a concern is that one site has a (6) page registration process, inserting to (8) tables.

My idea (aside from aleady checking for unique inserts on usernames/passwords/emails) is to check on the complete phone number.
To be used on a conditional show region on the second page of registration -- if the latest registrant entered the same phone number asmay aleady exist in the table, (previously enterered while registering on the other site and forgot about it) -- this 2nd registration page will not show due to the: count(*)

Starting with 'Area' code, is this practical/how to combine all three?
Anyone have better suggestions or experience on handling two site duiplicates?

Would there be any benefit in having ALL USERS (both new and prior from the "other" site) fill out the the 1st registration form (on the (6) page registration site) where they are then directed to login to complete the process?

'AREA' 'PRE' 'PHONE'

SELECT *
FROM `Members`
WHERE `Area`
IN (
SELECT `Area`
FROM 'Members`
GROUP BY `Area`
HAVING count(*)>1
)

note: some registrants have same company names fir satellite/regional offices

Avoid Zero In Interger Datatype
Is there any way to avoid zeros which automatically entered in database in those column those have integer data type.

or i want to enter - instead of those zero

How To Avoid Previous Results
I want to omit all results(id) from the first query in 2nd query , without using sub query...

How To Avoid Duplicate Records
I need to filter out duplicates for every 30 seconds. say i have two duplicate records within the 30 seconds limit. I need to show up only one. If there are identical records but with a different time settings(say above 30 seconds) then i need to display it. I need to restrict duplicate records within 30 seconds.

How To Avoid NOT EXISTS In MySQL 4.0.26
I tried to use NOT EXISTS but recently found out that it is supported since 4.1. Now I'm trying to avoid it but I can't figure out how to do this. The Problem is that I have a table with (among outhers) a column SessionId and action.

Action may be opened and closed. Now I want to get all those sessions which are in state open, i.e. which have no line with action = closed. My first attempt was:

select sessionId
from audit AS a
where action = 'opened' AND
NOT EXISTS (SELECT * FROM audit AS b WHERE b.sessionId = a.sessionId AND b.action = 'closed')

Could anybody give me a hint how to get an equivalient query without a subquery?

How To Avoid Repeat Typing
I would like to know how to avoid repeating
typing an SQL statement when an error occurs after execution. That is,
if an error occurs I should be able to retrieve the statement that I
had written and correct the mistake. It is agonizing to keep on
repeating a statement that can take five minutes to write just because
one mispelled a word or missed a comma. I use windows 98.

Avoid Repeat Typing
I am a newbie in MySQL. I would like to know how to avoid repeating
typing an SQL statement when an error occurs after execution. That is,
if an error occurs I should be able to retrieve the statement that I
had written and correct the mistake. It is agonizing to keep on
repeating a statement that can take five minutes to write just because
one mispelled a word or missed a comma.

Command Used In Avoid Retyping
I don't want to retype a command once I wrote it. Does anyone knowa about an option that would just that like in a dos shell where you can use arrows to get back any command you have entered.

Avoid Couples In The Resultset
I use a query like this:
select t1.id_topics, i1.id_indices , t2.id_topics from indices i1, indices i2, topics t1, topics t2
where (i1.ind ='test' and i1.id_urls=t1.id_urls and i2.id_urls=t2.id_urls and i1.ind=i2.ind and t1.id_topics<>t2.id_topics)

it gives results like those:
"id_topic1","id_indices","id_topic2"
36,682,34
37,682,36
36,682,37
37,682,34

I would like to eliminate the "inverted" mates of couples in the resultset. That means in the example
"36,682,37" should be eliminated since "37,682,36" is already part of the resultset. Is it possoble to express this request in the query?

Avoid Duplicate Records In Within 30 Seconds
I'm working with php. I have a auction site, more or less. I want to create all-time rankings. The idea is to display where a seller ranks (all time) in the number of sales.

So for example, I'd display
John Doe All Time Sales Ranking: #138

I'm not exactly sure how to go about this.

$query = "SELECT count(*) as counter, SellerName FROM sales GROUP BY Sellername ORDER BY counter DESC";

This query would give me the data to list all of the Sellers in desc order by the number of sales.

In php, I could probably count until the Sellername was equal to $Sellername (already defined in their profile page), but I was hoping there would be a way to do this entirely in MySQL.

How To Avoid Having Thousands Of Records In A Many-to-many Relation
I'm building a web administration system for my company. We keep all our contacts from other organizations in this system (stored in a MySQL database): name, addres, telephone etc.

One feature of the system is that you can select a number of contacts and collectively send them an email. This works fine.

But: some of my co-workers need to know which of the contacts has received a specific email from the system.
So I was considering a setup like the following:

Table one: contacts (name, addres etc.)
Tabel two: emails (subject, text, creation day, day of delivery etc.)
Table three: many-to-many table holding one row for each email that has been send to a specific contact (autonum ID, ID of email and ID of contact).

My problem is that I can predict that this table will have LOTS of records in no time, as my co-workers are sending out many emails to a lot of contacts.

So: are there any better ways?
I thought about storing all contact IDs that has received a specific email in a text field in that email's record in the database - but then I'm not sure of the performance when I have to find out if someone specific has gotten a specific email etc.

Cleaning Up MySQL Connections To Avoid 1040
Well, I'm trying to run
PHP

$result = mysql_query("SHOW FULL PROCESSLIST");
while ($row=mysql_fetch_array($result)) {
    $process_id=$row["Id"];
    if ($row["Time"] > 200 ) {
        $sql="KILL $process_id";
        mysql_query($sql);
    }
}

To clean up my connections, as I'm getting a 1040 error "too many connections". Of course, I can't run this until I can actually connect, unless there is a way around somehow. I don't have any admin rights, I just have a web-based "php my-admin" module to run the db.

Every page people access opens a mysql connection, and then it is closed with
PHP

mysql_close($connection);

. Would putting in
PHP

<?php mysql_close($connection); $NASI_connection = null;?>

Mysql Running All Queries Double ? How To Avoid ?
Sometimes, when my database server has been slow and building up hundreds of queries, I have the impression that mysql is running 'double' of triple . It's hard to explain, but when I look at mtop (a tool to see what queries are active and what time they take to finish) , I see a lot of queries that seem to hang and that are present more then once. Some of them have unique data in them that can not happen just by refreshing a form. When the peak moment is over, and I check my site, then I sometimes see for example the exact same forum post 3 or 4 times in the same thread. Needless to say, this causes a lot of extra load on my database.

I'm not sure what really happens, but it seems like mysql server is just piling up queries wich it can not process fast enough, and those queries seem to come in several times again. I use seperate webservers, but even if I reboot them all, the extra queries still come in as soon as the webserver is back up and php scripts start to work again. The only way to stop it all, is to restart mysql, but that is something that I can not just do very easily. I rely on several heap tables for my site and they need to be refilled with data from normal tables. So restarting mysql is something I only like to do at night, with very few visitors online.

I hope I'm explaining my problem well enough. And I hope there is a way for me to check what really is going on, and if there is a way to stop mysql from 'running double' ?

What Field Type To Use To Avoid Blank Spaces In Fields
Can you tell me the best field type to use here?

I've got a table in mysql with all 5 fields defined
as tinytext

Problem is when I export this to to a text file for notepad
each field is padded out by several blank spaces,
and i think my eamil program doesnt like this type of structue :
field1 , field2 , field3 , field4

Scheduling Replication To Avoid Bottle-neck Updates
We have a circular master-slave setup where any one of the 2 servers
can become master at any time (by human decision). The two servers are
placed at geographically different sites. The servers contain à number

of databases which are all replicated both ways.

When we have full usage of one master ~500 inserts/updates per second,
the bandwidth between our sites becomes a significant bottle-neck. This

we can accept at database level not on server level, ie
- if database A on site B has a lag of 30min because of important
activity on database A on site A, it is acceptable.
- if database B on site B has a lag of 30min because of important
activity on database A on site A, it is not acceptable.

Is there a work-around? We never have updates concerning 2 databases in
the same query.

Creating multiple mysql servers at each site could be one, but that
means some 10-50 servers on every physical computer. What side-effects
does that create?

How To Do A Loop
I want to build up a loop where I want to input a starting date and an end date and the loop runs for every day in between.

Using While Loop
I wan to know, how to iterate through the result of:

SELECT col_name FROM table_name;

using a while loop, inside a stored procedure?

Loop
I tried this to see if it would work first then shout if a problem. How can I do something like this?

for ($i=9;$i<15;$i++)
for ($i=0;$i<9;$i++)
for ($i=15;$i<20;$i++)
for ($i=20;$i<29;$i++)
for ($i=29;$i<38;$i++)
for ($i=38;$i<40;$i++)

} } } } } }

And be able to pass the same code within the loop system?

Inside IF NULL
I have this table:
------------------
id lang name
1 en name1
1 de NULL
2 en name2
2 de name3

I want to return all (WHERE lang='de') rows but if the (name=NULL) then replace it with name from the same id but lang='en'. Is it possible?

I have this query:

SELECT IFNULL(name, SELECT name FROM table WHERE lang='en' AND id=????) name FROM table WHERE lang='de'

Can you fix it?

While Loop Within Select?
SQL isn't coming for me today and maybe I'm being silly. Anyhow here is what I've got...

Table 1 is a schedule for a transportation service. Each record contains one field with the starting time of the trip and the stops along the way listed in one varchar field. So a couple records from the schedule might look like ....

How To Loop An Output
I am having a go at creating a database and learnign how to use php to get the info onto a webpage.

I have a testing server on my local host I am using. I have a database with a table in it called hotels and fields (i think 9 I made up)

Anyway, I am simply trying to pull name (row 2) website (row 7) phone (row 8) from the table and display it.

I can't seem to get the code right .. I have tried a few different ways .. below is my most recent attempt and I just get a last line error.

Can someone point me in the right direction so I can get going ?

Loop Time
how can I loop a time in mysql in a single query. Or is that possible?

Ex. I have a data like this
1:00 - 1:59 = 2 Sales
2:00 - 2:59 = 5 Sales
3:00 - 3:39 = 4 = Sales

I like to loop the time.

How To Get Different Queries In A While Loop?
i'm trying to make a php script that will show me the last 10 posts made on my phpbb board, it will show me the thread that post belongs to and the author of the post. I am trying to do it with the following loop:

$i = 0;
while ($i < 10)
{

$query="SELECT * FROM phpbb_posts ORDER BY post_id DESC LIMIT 10";
$result=mysql_query($query);
$post_id = mysql_result($result,$i,"post_id");
$topic_id = mysql_result($result,$i,"topic_id");
$query2 = "SELECT topic_title FROM phpbb_topics WHERE topic_id='$topic_id'";
$result2=mysql_query($query2);
$topic_title=mysql_result($result2,$i,"topic_title");
echo "$post_id <br> $topic_id <br> $topic_title";
$i++;
}

This works for the first result, but for the rest of the results it states:

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 1 on MySQL result index 6 in /home/website/public_html/php/index.php on line 41

How could I make this work?

Make A Loop
i got this table with different numbers, and i have to compare each of them to a value stored in a variable. when i get match, some statements run; however, i dont know how to get one value at a time! heres what the pseudo code looks like

@fixedvalue=5

repeat
if @fixedvalue = value i from rates then
do some statements;
i = i + 1;
until @counter = count (*) from rates;
do some more statements;

i havent found the way to identify rows! thought triggers repeated on each row, but they dont, and indexes are not actually indexes as in 1,2,3....2700.

Nested Loop
I have searched thru the forum but didn't find anything that could help me.

I have a database with air dates of shows from different years. My query looks like this

$res=mysql_query("select DISTINCT * from performance ORDER BY showDate");

The db has the following fields:

showID
showDate
bandID
year
date

Here is the rest of the code:

$num=mysql_numrows($res);

while ($row = mysql_fetch_assoc($res)) {

if ($prev_show_val != $row['date']) {
//update the show date here so no dates repeat
$prev_show_val = $row['date'];
echo " ";
echo '<font face=arial size=4 color=#2F4F4F>' . $row['date']. '';
}
}

which successfully prints out only the unique airdates from the db.

What i want to do is have the year listed once for each set of airdates from that year so it would display 1975 once and then all the dates from 1975. Then 1976 would display once and all the dates from that year, etc.

Using WHILE Loop In MySQL
I'm trying to write a script that will do a bunch of actions on my MySQL Server. I have been trying to get WHILE loops to work all day and I have had no joy whatsoever. I have upgraded my MySQL version to 4.1.6-gamma but that has made no difference.

I modified the WHILE loop example on the MySQL manual to this:

Code:

SET @v1 = 5

WHILE @v1 > 0 DO
SET @v1 = @v1 - 1;
END WHILE;

That is about as simple as it could be, but I get error messages when I try and run that:

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 'WHILE @v1 > 0 DO...

Does anyone have a working example of a WHILE loop in MySQL and from what version did they begin to support WHILE loops?

Loop Insert
I am running mysql from the command line. I guess in pseudo code, what I want to do is:

declare variable x
while i<x
insert
end while

Everytime i try to use the mysql syntax on the command line, it keeps giving errors as soon as I end the declaration (because of the associated ';').

Add Dates In Loop
but I can´t get it to work.
I have an table with columns named like this:

2004-12-01, 2004-12-02 and so on,

So I need to get the dates between two dates and update with the new values the columns with the same name as the dates I got.

$date = 2004-12-01;
$enddate = 2004-12-10;
for($date<=$enddate; $sSQL++
{
$sSQL = "SELECT DATE_ADD('$date', INTERVAL 1 DAY) date";
$sSQL = mysql_query($sSQL);
echo "$date";
then I should update my table like this:
$sSQLsql = mysql_query("UPDATE `disponibilidad` SET `$s` = 'w' WHERE `property` = 'Banana_Beach_3' ");

INSERT - Loop - 127
does anyone of you know wether there exists a restriction in mySQL to upload only 127 entries at once?

background:
I did a simple for-loop in PHP that shall upload about 2000 entries to my database but it keeps failing after 127 entries - no matter which variables I take for source.

And if so, how can I turn that "mySQL protection of"?

Fastest Row By Row Loop
what is the fastest row by row loop in a table apart from using a cursor ?

Looking For Tokens Inside A String
I'm trying to do a weird (?) thing with regexp and MySQL. For example,
i have a column with:

token1|token2|token3

(three tokens separated by pipes)
I'm trying to write an SQL query in order to look for the presence of
one of these tokens, that is "look for a string like 'tokenX' that is
preceded by the beginning of the line OR a pipe, AND that is followed
by the end of the line OR a pipe". I have tried this:

SELECT * FROM ... WHERE (... REGEXP "(^||)tokenX($||)");

but, of course, it doesn't work. So my question is: what's wrong ? My
regular expression, or my idea ?? There is a another way to obtain
what i want, that is to select a token within a string when tokens are
separated by anything but a comma ?

COUNT (*) Inside Select
I am doing something wrong i would like to do a count inside this select for example:

SELECT th.tid, th.tname, th.tmain
(SELECT COUNT(*) as postnumber FROM forumpost AS post WHERE post.ptid=th.tid)
FROM thread as th WHERE tmain=1

UNION Vs While Loop Question
On Edit: Please disregard this thread; I just stumbled over the problem.

* * * * *

I've been working with the following query (which works):
PHP

$res = mysql_query ("SELECT PxT.URL, PxT.Topic, PxT.Site, PxT.Live, ART.ID, ART.Articles, ART.Site Site2, ART.Live2 FROM px_topics PxT LEFT JOIN px_articles_topics ART ON ART.ID = Pxt.URL WHERE PxT.Site = ART.Site AND PxT.Topic = '$MyURL'") or die (mysql_error());while ($row = mysql_fetch_array ($res)){

As you can see, this query joins tables px_topics (which sets up dynamic pages) and px_articles_topics (which displays articles on the pages created by px_topics). I want to add two more pairs of tables to my query, for a total of six tables:

px_topics
px_articles_topics
px_people
px_articles_people
px_orgs
px_articles_orgs
The tables are virtually identical except that the field named Topics in table px_topics is named Person in px_people and Org in px_orgs.

So I started to work on a UNION query but ran into problems immediately. I had to drop the "or die (mysql_error());" clause to make it work, winding up with the following:

PHP

$res = mysql_query ("SELECT * FROM{ SELECT PxT.URL, PxT.Topic, PxT.Site, PxT.Live, ART.ID, ART.Articles, ART.Site Site2, ART.Live2 FROM px_topics PxT LEFT JOIN px_articles_topics ART ON ART.ID = Pxt.URL WHERE PxT.Site = ART.Site AND PxT.Topic = '$MyURL') } AS TPO");while ($row = mysql_fetch_array ($res))

But I get an error on the last line. I tried each of the following variations, without luck:

PHP

while ($TPO = mysql_fetch_array ($res))while ($row = mysql_fetch_array ($TPO))

I assume my finished query will look something like this, right?:

PHP

$res = mysql_query ("SELECT * FROM{ SELECT PxT.URL, PxT.Topic, PxT.Site, PxT.Live, ART.ID, ART.Articles, ART.Site Site2, ART.Live2 FROM px_topics PxT LEFT JOIN px_articles_topics ART ON ART.ID = Pxt.URL WHERE PxT.Site = ART.Site AND PxT.Topic = '$MyURL') UNION ALL SELECT ORG.URL, ORG.org, ORG.Site, ORG.Live, ART2.ID, ART2.Articles, ART2.Site Site2, ART2.Live2 FROM px_orgs ORG LEFT JOIN px_articles_orgs ART2 ON ART2.ID = ORG.URL WHERE ORG.Site = ART2.Site AND ORG.Org = '$MyURL') } AS TPO");while ($row = mysql_fetch_array ($res))

Anyway, I'm not sure if I just need to fix my WHILE LOOP or if there's a problem with my query.