MYSQL :: Simple Mysql Query Not Working

Simple Update Just Not Working /banghead
Background: I've been around the web for quite some time, but am new to PHP/MySQL. I recently purchased a zip code database for an upcoming project I'm starting here, and for some reason the CSV format turned all the zipcodes that start with a zero into 4 digits. No biggie, after hours of wrestling with cpanel MySQL import, then giving up and moving on to modifying the bigdump script I FINALLY got the import done. Now I'm trying to correct the 4 digit zip codes back into 5 digit ones.

Table name is ZipCodes, column name is ZipCode (is varchar(5) column). Here's the code I'm trying to use to update things, but it just does not perform the update for some reason.

update ZipCodes set ZipCode = Ɔ' + ZipCode where char_length(ZipCode) = 4;
I've spent a few hours now reading up trying to figure out what the hell I'm doing wrong, but to no avail. I'm sure the fact that I'm a newb at MySQL isn't helping, but is it something obvious that I'm just to dumb to see at this point?

Bug In A Simple MySQL Query
Having a problem with a very simple query I am trying to execute. Hopefully someone can shed some light on the issue.

SQL Query

SELECT tblUserInfo.PersonalID, tblUserInfo.mainPicLink, tblUserInfo.Location FROM tblUserInfo, tblCoreUser
WHERE tblUserInfo.Orientation = 'Straight'
AND tblUserInfo.Location = 'Hampshire'
AND tblUserInfo.PersonalID = tblCoreUser.PersonalID
AND tblCoreUser.usrSex = 'M'
LIMIT 10;
Error:

Unknown column 'tblCoreUser.PersonalID' in 'where clause'

Mysql Simple Query
I am doing a project in mysql and php..
While inserting the data, i made a mistake of inserting date values into the month field and viceversa. can i swap it with a query. I cant take a backup of this table.. because its very big and the webspace for mysql database for me is running very low.

ONE MORE THING :- there are many tables almost 565... can this all be swapped in a single query or procedure.

Simple MySQL Query
I want to do this simple query but I cant remember how... (Ive done it before)...
UPDATE users SET credit = '$credits+10' where user='$user'
Basically whatever is in the credits column gets 10 added to it and the column is updated

Simple Mysql Query
I know this will be simple but I just cant get it to work and I am being tested here by my line manager.
Our database is named: lsql_Category and we have a table column named: Keywords
We have thousands of rows
The column Keywords has data in most of the rows and some without any data at all. Normally assorted and various text words.
I want to clear all data from this coloumn but DO NOT want to delete the coloumn cos we want to input data at later time.
The result should be that the column Keywords remains but will not have any data.
Can someone help? I think it is a select command and a where command but that is all I know.

PLEASE HELP*SIMPLE - Mysql INSERT QUERY
And YES its connected to mysql DB i need help (a fix) ASAP thanks

<?

$sql = "UPDATE `games` SET `sponsor` ='$WebsiteURL' WHERE `gameid` =$GameID LIMIT 1";

$result = mysql_query($sql);

echo "$result";

?>

Need Help Formulating (somewhat) Simple MySQL Query
I'm more experienced in PHP, but not very experienced in MySQL. I really know nothing of joins, left joins, right joins, unions, and so on. Naturally, some of my code is becoming extremely cluttered because I don't know anything about these things. Sooo... I really need to get off my rear and learn how to manage data better.

I have two tables: users and news.

NEWS:

news_id
news_title
news_authorid
news_timestamp
USERS:

user_id
user_username
user_displayname
user_password
You'll notice that in each and every one of my news posts, it stores the author id of the user that posted the news. It'd be nice to just enter in the name, rather than an id, right? But the problem is, usernames are becoming somewhat dynamic, so it's useful to use the user's id as reference so we can select whatever we want about a user.

My question is, how can I "join" these two tables to get the user's display_name when selecting and displaying data from my news table? I could go like this:

$myfancydbclass->query("select * from news order by when_it_was_created");

while ($this)
{
$myfancydbclass->query("select * from users where user_id = $row->news_authorid");
}
But is there a way I can "join" the two tables so I dont have to run two queries just to get the news and user's display name (use fields user_id and news_authorid to join the two)? I hope I've made my question clear. If you don't understand what I'm asking, ask me a question back. As you can tell, I know nothing about this. Basically, what would the query be to join these two tables?

MySQL Crashes On Simple Query
I am setting up MYSQL/ MS 2000 server on a p4 2.6 w/ 512 RAM/80gb server.

I was testing my server, i tried to insert 7million + rows in a table, then simply try to double it by "insert into .... select * from 'same table'". I understand it could take a while to double the 1.3 gb table. howver, the MYSQL crashes after several minutes and I can't even connect back again. the error msg is 'localhost connection lost'

it happens a few times , and everytime i need to reboot the machine to get it working.
How come? BTW, i am using 4.1 alpha.

Another question, i tried to be patient and delete 5millions rows from the same table, it took about 4 mins to do it. BUt after the console told me it is done, I tried to look at the table status from the Mysql CC, i found that there are still a large number of rows in the table, and the "select count(*) "statement stills take a while to do it.

If i refresh in the mysql CC, the number of row keeps decreasing and after a while, it finally go to 1000 row(which is what I expected). But how can this happens?

I didn't experience such stuff in Oracle and MySQL. As the console told me it is done, it is done.

FYI, I tried the run the same select count(*) statement, it takes less and less time to run until the table is back to 1000 rows. It looks like that mysql is still deleting the rows after it tells me it is done...

Query Cache Not Working: MySQL 5 / Windows XP
I just installed MySQL 5, and its running great except that the query cache is not working, despite the configuration excerpt from my "my.ini"
below:

query_cache_size=5M
query_cache_limit=2M
query_cache_min_res_unit=4096k
query_cache_type=1

From the MySQL command line, if I enter: SHOW VARIABLES LIKE
'have_query_cache';

I get:
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+

Also, doing a: SHOW STATUS LIKE 'Qcache%'; gives me:

+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 5234168 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 450 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+---------+

I figure the "Qcache_free_blocks" and "Qcache_total_blocks" values of 1
are creating the problem; it's basically telling me that there are no
(okay, 1) free blocks to put my query. However, these values never
change.

The query cache is not vital to me, but it does improve performance, and
I'd like to get it going....

Simple Sql Question: Using A Query Result As A Query Variable
EDIT: it works now, I had an error in my code, not my method.

I have a very simple question.

I have 2 tables: 'users' and 'posts' with the following structure:

users: id, username, email_address
posts: id, user_id, post_title, post_text

in a my own mind's mysql, I would like to:

SELECT posts.id, posts.user_id, posts.post_title, posts.post_text users.username FROM users, posts WHERE posts.user_id = users.id

I usually do one query for the post data, and then, based on the use_id record, do another of the users table, but today, I'm being forced to do them in one swoop.

MySQL Query Cache: Should I Cache Small, Simple Queries Or Only Complex Ones?
Query cache works great for long, complex queries, but should I also be caching the simplest of select queries.

For example let's say I had a table with 100 records and I needed to select something using a unique key:

SELECT name FROM products WHERE id = 3
Is caching the above pointless, especially in terms of wasted cache memory, considering how basic it is?

Simple SQL Query
I was wondering if anyone could help me with this SQL query?

I'm making a football prediction website and I want to be able to display the weekly fixtures in a GridView so the admin can edit, insert, etc.

This is a simple version of the two tables that I have: Fixtures and Clubs...

CREATE TABLE Fixtures
(
MatchID Int PRIMARY KEY,
HomeClubID Int,
AwayClubID Int
)

CREATE TABLE Clubs
(
ClubID Int PRIMARY KEY,
Name Nvarchar(50)
)

.. where HomeClubID and AwayClubID are actually foreign keys to ClubID.

My Problem is that I'd like to create a query that will show each fixture with both the names of the home and away clubs on the same row, eg:

| MatchID | Name | Name |
|--------------------------------|
| 1 | Arsenal | Man Utd |
|--------------------------------|
| 2 | Liverpool | Chelsea |

... etc.

Help With Simple Query
I know this is easy to accomplish, but my brain is fried and I can't figure it out.

I have a few markets (areaID) that vendors belong to. Within these markets, vendors are assigned categories (catID).

I am trying to do a SELECT on the vendors table to find out the total number of vendors to a particular category, within a particular market. So essentially, I want to know the number of vendors within each category within each market. Does that make sense?

Can Anyone Help Me With This Simple Query?
In my database. I want to pull out one record:

Where the date I wish to publish a story where the published_web_date(date) is less or equal to now
and
The published_web_time(time) is less or equal to now

However, using the below query data is nomatter what the published_web_date is if the published_web_time has not elapsed then nothing is shown.

select * from cms_stories
WHERE section = 'news'
AND published_web_date <= NOW()
AND published_web_time <= NOW()
ORDER BY story_id
DESC LIMIT 1";

A Simple Query?
In table "vb_user" I want to pull the data in field "username" and copy that username in another table called "vb_userfield" in a field called "field5". Both tables share the field "userid" which is how the relation is made. I know this is probably very simple but man... I just can't seem to pull it off.

I've got about 500 members and I really want to populate that field "field5" with thier username without having every one of them doing it manually.

Simple Query
I was curious as to what is the best way to determine if a SELECT query was successful in retireving any rows? I've been using..
IF(!$getline){echo "no rows found"
but if there were rows found I would have to run the same query again so I can print them. Is there another way?

$get="select * from orders'";
$getresult=mysql_query($get, $link) or die(ErrorDB($realname));
$getline=mysql_fetch_array($getresult, MYSQL_ASSOC) or die(ErrorDB($realname));

Simple SQL Query
I am looking for some help on a simple (yet beyond my reach) SQL query that I can run via phpMyAdmin whenever I need to.
In a nutshell, I have a Database called A with table B, in which fields C and D sit. For each record, field C has an integer and field D has a human-readable date/time entry in the format: 2005-08-23 17:35:33.
It is important to note that the above date part is YYYY-MM-DD as the convention might be different in your locality.
Anyway, I am looking for an SQL query that will go through the table and whenever the date/time shown in field D is no more than 5 days ago, to add X (some whole number chosen and manually specified in the query each time it's run) to the value in field C

Simple Query
At least I think it should be simple, but I can't get it.

SELECT * FROM 'tableA'
WHERE 'tableA.id' = 'tableB.target_id'

Which returns an empty set, though

SELECT * FROM tableB
WHERE target_id = "b327f34a-54ea-071a-3b9d-451810085fb9"

and

SELECT * FROM tableA
WHERE id = "b327f34a-54ea-071a-3b9d-451810085fb9"

both work. That is to say that there is a record in tableA WHERE, tableA.id = tableB.target_id for AT LEAST one record in tableB

Query Not Working In 5.0 That Worked In 4.0
I've created a development site for my website that uses mysql 5.0.18 and my live site uses 4.0.16. I have a query I can run without problems on the live site but is giving me errors on the development site and I was hoping someone might know what the problem is. It must be some change to the syntax standard I suppose. Thanks for your help!!!

1054 - Unknown column 'p.products_id' in 'on clause'

select count(p.products_id) as total from products_to_products as pp, products p, products_description pd, manufacturers m left join specials s on p.products_id = s.products_id where pp.products_id=&#56703;' and p.products_id = pp.related_products_id and pd.products_id=p.products_id and p.products_status = Ƈ' and pd.language_id = Ƈ' and m.manufacturers_id = p.manufacturers_id

ANDs In Query Not Working
I am working with this query:

SELECT * FROM reviews WHERE gID = 'nothing' OR gID = '825' OR gID = '864' OR gID = '632' OR gID = '880' OR gID = '839' OR gID = '534' OR gID = '680' OR gID = 'x' AND type = 'online' AND status = '1' ORDER BY RAND() LIMIT 0, 10

With everything going on here, the most important is that only rows with status=1 are selected. However, I am getting some rows with status=0 in my results. Is there something wrong with the ANDs in my query?

Simple Query That I Can't Figure Out
First off, here's my table:
CREATE TABLE order_info (
ID int(10) NOT NULL auto_increment,
ordernum int(10) NOT NULL default Ɔ',
item_code text NOT NULL,
item_qty int(5) NOT NULL default Ɔ',
item_price text NOT NULL,
have int(5) default NULL,
standby int(5) default NULL,
backorder int(5) default NULL,
refund int(5) default NULL,
`status` int(2) NOT NULL default Ƈ',
PRIMARY KEY (ID),
KEY ordernum (ordernum),
KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=0 AUTO_INCREMENT=477150 ;
I would like to make a simple query that returns ordernum's that match this critera:
All of the items have a 'status' of 3
Have at least 1 'refund'
and I would like the ordernum's returned to be distinct.

Here's the query I had before the requirement of the 'refund' column:

SELECT
DISTINCT order_info.ordernum
FROM
order_info
WHERE
order_info.ordernum NOT IN (
SELECT
order_info.ordernum
FROM
order_info
WHERE
order_info.status != 3
)
AND
order_info.status = 3
I tried adding:

SELECT
order_info.ordernum
FROM
order_info
WHERE
order_info.ordernum NOT IN (
SELECT
order_info.ordernum
FROM
order_info
WHERE
order_info.status != 3
)
AND
order_info.status = 3
AND
SUM(order_info.refund) > 0
GROUP BY
order_info.ordernum
But it returns incorrect information (orders with 0 'refunds').

What is the correct way to make this query?

A Simple Query Question ...
The following returns one row -

PHP

SELECT ab.id, ab.menu_name, ab.keywords, bb.content, NULL , NULL , NULL , NULL , NULL , NULL
FROM menu_management AS ab
JOIN dynamenu_content AS bb ON ab.id = bb.menu_id
WHERE 1 =1
AND bb.publish =1
AND ab.version = 'fr'
AND ab.menu_name LIKE '%Nanotechnologies%'
OR ab.keywords LIKE '%Nanotechnologies%'
OR bb.content LIKE '%Nanotechnologies%'
LIMIT 0 , 30

But the following returns zero rows

PHP

SELECT ab.id, ab.menu_name, ab.keywords, bb.content, NULL , NULL , NULL , NULL , NULL , NULL
FROM menu_management AS ab
JOIN dynamenu_content AS bb ON ab.id = bb.menu_id
WHERE 1 =1
AND bb.publish =1
AND ab.version = 'fr'
AND (
ab.menu_name LIKE '%Nanotechnologies%'
OR ab.keywords LIKE '%Nanotechnologies%'
OR bb.content LIKE '%Nanotechnologies%'
)
LIMIT 0 , 30

In fact I am trying to make a query (which will later be with other queries thro' UNION ALL) where upto ab.version = 'fr' will remain constant, but the following part will change.

Means, among ab.menu_name, ab.keywords and bb.content, there may be all the three, or two or one condition only, depending on user's input. Also, the condition between them can be OR or AND, depending on whether user tends to search for all or any matches.

Simple Between Query Problem
I have multiple fields on my form where a user submits a minimum and maximum value. Such as minimum price and maximum price, so I use a Between in my queries like this.

SELECT COUNT(*) AS 'numrec' FROM tblmls WHERE mlsstatus IN('Active', 'New', 'Back on Market', 'Price Change') AND mlsclass = 'Residential' AND mlslistprice BETWEEN ?' AND ?'
My question is, is there a way to do wild cards using a between statement? Or is there a better way to do this?

Currently if they don't put anything in I don't run that part of the querie, but if they put in a minimum but no maximum I just autosubmit a large number. If they put in a maximum but no minimum I just submit a 0.

Simple Query Problem
I'm a newbie having trouble properly executing a simple query.

I'm trying to make a pathway to prevent tennis players from signing up more than once on a specific date.

Here is my

PHP

$play_date=($_POST['play_date']);
$user_id=$_SESSION['user_id'];

// Make the query to check if the player already signed up on that date.
$query = "SELECT user_id, play_date
FROM table
WHERE user_id=$user_id AND play_date=$play_date
";
$result = mysql_query ($query);
if (mysql_num_rows($result)>0) {

echo '<p><font color="red" size="+2">Sorry, you already signed up to play on this date!</font></p>'

} else {

Although the above code runs, it doesn't prevent one from signing up twice on the same date.

Simple Query Trouble
I have written a simple select query...but it is not doing what I want it to:

$query = "SELECT * FROM helpdeskticket WHERE (`customerid` = '$customerid') AND (`helpdeskticket`.`status` = 'Assigned') OR (`helpdeskticket`.`status` = 'Unassigned') OR (`helpdeskticket`.`status` = 'Can Be Closed') ORDER BY `helpdeskticket`.`ticketid` DESC

Because its wrong i'll explain what I wish it to do. Select all fields from helpdesk ticket where customerid is equal to $customerid, and the status is either assigned, unassigned or can be closed. Then order by ticketid descending.

At the moment it seems to be messing around with the AND and the OR, and is selecting everything from the database table, ignoring the customer id.

Simple Not Distinct Query?
I have a very large product table and believe that some of the fields contain duplicate values when they shouldn't. So I'm trying to create a simple query to identify those that do.. ie something like

Select modelnumber from tbl_product where modelnumber is not distinct

OR

Select NOT Distinct modelnumber from tbl_product.

These obviously don't work (Otherwise I wouldn't be asking for help!), and was wondering if there is a not distinct function that im missing or misusing?

Simple Count Query
I am trying to use the below query to count the number of images held for each book. Unfortunately it is only returning the books with images. I would like all books returned even if they have no corresponding image.

SELECT tblBooks.Title, tblBooks.Description, tblBooks.Author, tblBooks.Annotation, tblBooks.Collection, tblBooks.Price, tblBooks.SortID, tblBooks.Heading, Count( tblImage.ImageID ) AS CountImage
FROM tblBooks
INNER JOIN tblImage ON tblImage.BookID = tblBooks.BookID
GROUP BY tblBooks.BookID

Simple Null Query
How do you say I want to select all data from this column but only show the data where the data is not equal to null.
I have;

SELECT prevleaving1
FROM `jobemployment`
WHERE prevleaving1 IS NOT null

Update Query Not Working: SQL Error
i'm trying to do an update such as ...

Query Cache: Simple Question
I just have a simple question about MySQL QQ.

What I want to know is: Is the query cache only setup to apply to queries that stay the same?

PHP

"SELECT one,two,three FROM table LIMIT 1"

(Standard query, no WHERE clause, works the same for all visitors)

But a Query like this then doesn't stay the same

PHP

"SELECT one,two,three FROM table WHERE id='".$id."' LIMIT 1"

Since the ID value depends on a php variable, thus is different each time, then the query is in fact not the same.

Is the use of the query cache still beneficial in a case like this?

Simple Query? Why Is My Hair Falling Out?
Im trying to pull all records from table a and table b for 14 hrs now. It seems so simple?
both tables are indexed with the fields 'classid' and 'states'
actual results should be 36 records as you can see that is not the case.
Here is what ive tried so far:

SELECT * FROM classifieds_g WHERE states='AZ'

records 4

SELECT * FROM classifieds WHERE states='AZ'

records 32

Samples and results of what I've tried (should return 36 records)

SELECT * FROM classifieds_g, classifieds WHERE classifieds.states='AZ' and classifieds.states = classifieds_g.states

records 0

SELECT classifieds.states, classifieds_g.states FROM classifieds_g, classifieds WHERE classifieds.states='AZ' and classifieds.states = classifieds_g.states

records 0

SELECT * FROM classifieds_g, classifieds WHERE classifieds.states='AZ' GROUP BY classifieds.states

records 1

SELECT * FROM classifieds_g, classifieds WHERE classifieds.states='AZ'

records 4000

SELECT DISTINCT * FROM classifieds_g, classifieds WHERE classifieds.states='AZ' and classifieds_g.states='AZ'

records 128

SELECT * FROM classifieds_g, classifieds WHERE classifieds_g.states='AZ' and classifieds.states = classifieds_g.states

records 0

SELECT * FROM classifieds_g LEFT JOIN classifieds ON classifieds.states = classifieds_g.states WHERE classifieds_g.states='AZ'

records 128

SELECT * FROM classifieds_g INNER JOIN classifieds ON classifieds.states = classifieds_g.states WHERE classifieds_g.states='AZ'

records 128

SELECT * FROM classifieds_g LEFT JOIN classifieds ON classifieds.states = classifieds_g.states WHERE classifieds_g.states='AZ' GROUP BY classifieds.states

records 1

SELECT * FROM classifieds_g LEFT JOIN classifieds ON classifieds.states = classifieds_g.states WHERE classifieds_g.states='AZ' GROUP BY classifieds.classid

records 32 (all the same)

SELECT DISTINCT * FROM classifieds_g WHERE states IN(SELECT states FROM classifieds WHERE states='AZ' )GROUP BY states

records 1

SELECT DISTINCT * FROM classifieds_g WHERE states IN(SELECT states FROM classifieds WHERE states='AZ' )

records 4

Issue With Simple SELECT Query
i have three rows in my articles table. for some reason the query below does not show the first entry in that table, only the last two. is there an obvious reason why this would be the case? im displaying just the $title and the $id, simple enough! i can post database structure if required. stumped!

$queryGetArticles = "SELECT * FROM articles";
$resultGetArticles = mysql_query($queryGetArticles) or die ("couldnt get the data");
$row = mysql_fetch_array($resultGetArticles,MYSQL_ASSOC);

echo "<div id='stories'><ol>";
while ($row = mysql_fetch_array($resultGetArticles)) {
extract($row);
echo "<div class='story'><li><h3>$title (id: $articleID)</h3></li></div>";
}
echo "</ol></div>";

Having Problems Woth This Simple Sql Query!
hi, i have two tables essentially they are the same but one gets all normal file details placed in to it and the other has all the files that have been attached to comments put in to it...it just made programming far easier this way....

Now this seems like such a simple query but i just cant get it to work...

I want to select file_name,file_type, and upload_id from both tables so i can create an archive of all files posted to the site...how do i do this....

its something like

SELECT file_name,file_type,upload_id from uploads, comment uploads ORDER BY date_submitted DESC LIMIT 30

but thats obviously not it....i also tried a join query:

SELECT uploads.*, upload_comments.* from uploads INNER JOIN comment uploads ORDER BY date_submitted DESC LIMIT 30

Simple Select Query Take 5 Secons
This select i've got take 5 seconds and i really don't know why!!!!

The number of rows in any table is NOT bigger than 50 records!

SELECT DISTINCT(j.id) as idunic,j.* FROM jobs j
INNER JOIN jobs_categs jc ON j.id = jc.id_job
INNER JOIN jobs_type jt ON j.id = jt.id_job
INNER JOIN jobs_cities jci ON jci.id_job = j.id

WHERE j.status = 'OK'
AND j.deleted = 'NO'
AND j.suspend = 'NOK'
AND j.date_end > NOW()
AND jci.id_city = ƌ'

ORDER BY j.date_begin DESC LIMIT 0, 25

Simple Query Question: LIMIT
I'm looking to list all the rows in a database, but skipping the first 5. I know that I can use "LIMIT 0,5" to get the first 5 results, but what query do I use to list everything from 6th result onwards?

Using A Simple 'function' In SELECT Query
Is there any way of creating a SELECT query which has as it's result a column which is comprised of a function whos inputs are the values in other results columns.
For a simple example - a four column output, x, y, x+y, x/y
SELECT A.x, A.y, A.x+A.y, A.x/A.y FROM A
This is works fine, as is, and produces the correct results. However, as the function gets more and more complicated and the A.x and A.y turn into sub-queires (and generally the expressions all become larger and then more unwieldy) not only does the function get very painful to write and debug but the query times rise rapidly also.
Can alisas' be used here (or any other methods people know of) to refer to a columns' results to ease writing the functions and keep the load on the DB to a minimum?

Query Of Gmdate Not Working... Is My Syntax Correct?
$query="SELECT distinct(id),subject,view, postdate FROM threads where gmdate(Y-m-d H:i:s, postdate + 3600) >= date_sub('".$current_date."', interval 7 day) order by view desc limit 0 , 5 ";

I tried to get my postdate to compare with the current date in order to display the most view topic in last 7 days in my page...

however, the above coding is not working...

Query To Display The Rows As Colums Is Not Working!!
I want a select query to get two columns. but i need to get these columns one below the other that is consider that i have a table student in that i have 2 columns name mark.
Now i want the result as

Name Raj Rina Tina
Marks 80 90 70

I tried the --vertical option of mysql and G option in the query.

for example when i tried

select *from studentsG

it displayed

*********** 1. row ***********
name: Raj
marks : 80
********** 2. row ************
name: Rina
marks : 90
********** 3. row ************
name: Tina
marks : 70

but i want to display

Name Raj Rina Tina
Marks 80 90 70

Order By Not Working Correctly, Query Problem?
I am using the following query to get a list of anime names and related info from my mysql db. As anime are sometimes known by several names (the actual japanese kantakana/kanji, the romanji translation and the english word). So the info_anime table has all the info about the anime and the info_animename just has the relevent animeid and the name.

The query below is ment to check all the names in info_animename and join them to info_anime.

SQL
SELECT n.*, a.* FROM info_animename AS n, info_anime AS a
WHERE
a.animeid = n.animeid
ORDER BY ".$_GET[orderby]." ".$_GET[order]."

Now all the names are displayed properly with the various info but if you look here:

Live Anime - Anime List

You will see that the names are not in alphabetical order, for the most part they are just anime with several names are not working correctly if you look at the bottom of the page you will see:

Yakusoku no Basho Kumo no Mukou followed by
Beyond The Clouds, The Promised Place

Which are the same anime under different names.

Simple Message Board Query Question
my table "messages" looks like this:
id
parent_id
root_id
title
author
timestamp
Maybe it is possible to pull out latest threads with last post data
My query only shows replies and last post time, but I want a complete row of the last post
SELECT threads.*, COUNT(posts.id) - 1 as replies, MAX(posts.time) AS lastpost
FROM messages AS threads
LEFT JOIN AS messages AS posts ON posts.root_id = threads.id
WHERE threads.parent = 0
GROUP BY threads.id
ORDER BY lastpost DESC

Change To Simple Query Causing It To Run Show, Help?
I m looking for some help if possible. I ve got a very simple query which just outputs all the records from the table (tbl_addresses). It has 8587 records.

SELECT a.ID, a.address, a.datecreated, a.datemodified
FROM tbl_addresses as a
WHERE a.statusid = 1
LIMIT #arguments.pagesize# OFFSET #arguments.startrow#

I ve been asked to update the query to get the name field from another table called tbl_organisations. This table has 8883 records. There is a link table to relate the two (lnk_addresses). This table has 8243 records.

I ve updated my query to the following:

SELECT o.name, a.ID, a.address, a.datecreated, a.datemodified
FROM tbl_addresses as a
LEFT JOIN lnk_addresses as la ON a.id = la.addressId
LEFT JOIN tbl_organisations as o ON la.orgId = o.id
WHERE a.statusid = 1
LIMIT #arguments.pagesize# OFFSET #arguments.startrow#

The query now runs at snails pace and takes minutes to display anything. Even If I remove the 2nd join (LEFT JOIN tbl_organisations as o ON la.orgId = o.id) its slow so I guess its related to the tbl_addresses and lnk_addresses.

Join 2 Simple Queries Into A Single Query?
I would like to perform both these tasks in a single query, is there some kind of AND ? to chain multiple test criteria into a single query?......

Seemingly Simple FULLTEXT Query Over Multiple Tables
Can't seem to get my head around this query. Here's what I have:

I have 2 tables:

TABLE a, TABLE b:

Table a
Columns: UID, FName, LName, Bio

Table b
Columns: UID, Specialty

I want to search these FULLTEXT Indexed table columns (bio, Specialty) from the different tables for a string, and output the resulting UID and Username, one row only per UID (UID matches between tables)...

To put it another way, if the search string is found in either of the 2 columns in the 2 tables, I would like to output the UID and FName from TABLE A.

SELECT DISTINCT E.UID, E.FName, E.LName
FROM contact AS E, brg_uid_specialties AS C
WHERE E.UID=C.UID
AND MATCH (E.Bio) AGAINST ('bubble noodle Oranges')
OR MATCH (Specialties) AGAINST ('bubble noodle Oranges')"
The resulting output from my sample tables is an enormous dataset (when it should only get 1 hit), and I can't figure out why... .

Prefix Blank Lines To A Simple Select Query
I would like to display a certain number of blank lines before the output of a select query, ie:

SELECT Var1, Var2 FROM SOME_SOMETABLE UNION ...?

Extending Simple Query With Specific COUNT() From Second Table
Currently i basically do following:

-----------------------------------
SELECT cat_id, cat_name FROM categorytable WHERE blah=something ORDER BY pos ASC

while(FetchRowAssoc())
{
SELECT COUNT(*) as itemcount FROM itemtable WHERE category=cat_id

echo cat_it, cat_name, itemcount, blah... etc.
}
------------------------------------

I fetch some info from the category table and then I want to know how many items I have in the 2nd table which are associated to the categories in question from the first table.

Can this be done with a single query statement, so i get from FetchRowAssoc the cat_id, the cat_name and the count of the items for this category?

"SELECT t1.cat_id, t1.cat_name, COUNT(t2) as itemcount FROM categorytable as t1, itemtable as t2 WHERE t1.blah=something ORDER BY t1.pos ASC " <- like this but working, i really don't get how to properly write the syntax there... also didn't found a useful example with googling.

Trying Simple Embedded Query ... Get Syntax Error Using PhpMyAdmin
I am trying to do this in the SQL page of phpMyAdmin, but I get a syntax
error. Can someone please help me out ?
Thanks for your time and efforts!
JC

INSERT INTO `Test` ( `pkey` , `col2` , `col3` )
VALUES (
SELECT max( `pkey` )
FROM Test, `rrrrrrrrr` , `wwwwwwwww` )

pkey is the primary key, not null, varchar 11
col2 and col3 are both varchar 25, and can be null.

Also, the "Select Max('pkey') from Test" query works fine as a stand-alone
query.

This is the exact error I get from phpMyAdmin.

Error
SQL-query :
INSERT INTO `Test` ( `pkey` , `col2` , `col3` )
VALUES (

SELECT max( `pkey` )
FROM Test, `rrrrrrrrr` , `wwwwwwwww`
)
MySQL said:

#1064 - You have an error in your SQL syntax near 'SELECT max(
`pkey` )
FROM Test, `rrrrrrrrr` , `wwwwwwwww` )' at line 3

Working Of Mysql
can any one explain me in details how mysql engine works

PHP Not Working With MySQL
Just starting out and I have PHP, Mysql, and Apache installed. PHP and MYSQL dont seem to be working together. Everything seems to be running fine and I edited the httpd.conf in order to allow apache to process php requests.

This is what I am doing when I start mysql...
c:/net start mysql
c:/mysql/bin/mysql -u root -p
Then I log in and it accepts the log in.

When I create a sample php program like "Hello", it works.

But when I do a simple sql connect:

<?php
$conn = mysql_connect("localhost","root","tntboy");
echo $conn;
?>

I get nothing! Nothing at all comes up on the screen.
Any ideas? Why is it not connecting to the database?