[Help with a QUERY]

Thank Minase, that's almost it

 

The only thing I can't figure out now, is how to get it to loop the members rows once the last once has been inserted into "scores".

 

So if the members data was

 

ID     NAME

1      David

2      Bob

3      Sarah

 

then when ID 3 has been added to the "scores" table, it should then loop back round to "David"

 

Does that make sense?

 

Thanks superstars

[Help with a QUERY]

So how can I do a ID + 1?

 

Also, when it gets to the last row of the members table, how can I ask it to looop around to first row of the members table?

 

Thanks very much

[Help with a QUERY]

So how can I do a ID + 1?

 

Also, when it gets to the last row of the members table, how can I ask it to looop around to first row of the members table?

 

Thanks very much

[Help with a QUERY]

Nope, sorry.

 

The last entry in my "scores" table is

 

(5, '2');

 

which means that is member number 2, who is

 

(2, 'Bob'),

 

so what the query should do, is display

 

(3, 'Sarah');

 

because she is next in the members database.

 

If the last entry in my "scores" table was

 

(6, '3');

 

then that would be display

 

(1, 'David'),

 

because of a LOOP happening to the members database (ie: Sarah is the last member in the "members" table, so it should loop back to the first row, being David)

 

Does that make more sense?

[Help with a QUERY]

Hi

 

Nope, sorry that didn't work.

 

Let me explain some more.

 

Firsly here is an export on my tables

 

CREATE TABLE `members` (
 `id` int(100) NOT NULL auto_increment,
 `name` varchar(255) NOT NULL,
 PRIMARY KEY  (`id`)
);

INSERT INTO `members` (`id`, `name`) VALUES 
(1, 'David'),
(2, 'Bob'),
(3, 'Sarah');

CREATE TABLE `scores` (
 `id` int(100) NOT NULL auto_increment,
 `member_id` int(10) NOT NULL,
 PRIMARY KEY  (`id`)
);

INSERT INTO `scores` (`id`, `member_id`) VALUES 
(1, '1'),
(2, '2'),
(3, '3'),
(4, '1'),
(5, '2');

 

So what my query should do is return a member at a time.

 

If you look in the "scores" table, you will see in the "member_id" field, it stores the ID number of the each members from the "members" table.

 

So if you look at the last row of "scores" table, you will see the value for the field "member_id" value is set to 2, this relates to member ID 2 of the "members" table, which is "Bob".

 

So what my query should do, is return "Sarah" as she is listed after "Bob", if "Bob" was the last entry in the "scores" table under "member_id", then it would then return "David" as he is next in the sequence, due to the LOOP.

 

Does that make more sense?

 

Thanks

[Help with a QUERY]

Nope, that didnt seem to work budimir

 

Does my post make sense?

[Help with a QUERY]

What I tried is

 

SELECT m.id, m.member_id FROM members m LEFT JOIN scores s ON s.member_id = m.id ORDER BY s.id, m.id ASC LIMIT 1

 

But it doesnt seem to loop, im sure i've done something wrong

[Help with a QUERY]

Hi Budimi

 

Thanks, I know about the JOIN, I have been trying with that, but not getting the result I need.

 

But its more about getting one result at a time and also getting it to loop.

 

Does my post below make much sense to you?

[Help with a QUERY]

Hi all

 

Really wondering if anyone can help me as im totally stumpted on this.

 

Basically I have a "members" table, which looks pretty much like

 

MEMBERS

ID    NAME

1      David

2      Bob

3      Sarah

 

this is a list of all my site members.

 

I then have another table called "scores", which basically stores an ID number from the members table, an example looks like

 

 

SCORES

ID    MEMBER_ID

1      1

2      2

3      3

4      1

5      2

 

What I want to do is run a QUERY which basically displays a name of a member on screen, such as "David", but displays the member after the last member that appears in the "scores" table, but then loop it.

 

If you look in my "scores" table, you will see row 5 has the member value of 2 which relates to "Bob" in the "members table", so what the QUERY would show would be "Sarah" as she appears after "Bob".

 

If the last row in the "score" table has contained "Sarah" who is ID 3 of the "members" table, then the next result to show would be "David" as he is the the next one listed in the "members" table (when you loop it), then "Bob", then "Sarah" and so on.

 

The scores table after time would look something like

 

SCORES

ID    MEMBER_ID

1      1

2      2

3      3

4      1

5      2

6      3

7      1

8      2

9      3

 

Does that make sense? Can anyone help or give any advice?

 

Been trying to crack this for the last 5 hours with no luck.

 

Thankd in advance

 

Dave

[[SOLVED] Query name]

Oh thank you very very much

[[SOLVED] Query name]

Hi all

 

I have a simple table, which looks like

 

 

id    first_name      surmae

--------------------------

1      david            walker

2      sarah            turner

3      suzy            brick

 

I then have a HTML form which has one input field for "full name".

 

How can I write a select query which allows me to combine the first_name and surname fields of the database and do a full name search

 

hope that make sense, any help would be great

 

thanks

 

dave

[[SOLVED] Query DOB on Age]

thanks

[[SOLVED] Query DOB on Age]

Hi all

 

I have a table called "members" and a field called "dob", is it possible to do a query to say get the age of any members from 30 to 40 using the "dob" field? The "dob" field is stored like

 

1975-05-18 (YYYY-MM-DD)

 

Can anyone help?

 

Thanks

 

Dave

[[SOLVED] QUERY HELP]

Nice one Andy, cheers man

[[SOLVED] QUERY HELP]

Hi all

 

I have this very simple login script

 

// check login form has been POSTED
if(isset($_POST['userlogin']))
{
// check login details
$total_query = "SELECT COUNT(*) as Num FROM `logins` WHERE username = '".$_POST['username']."' AND password = '".$_POST['password']."'";
$total_results = mysql_result(mysql_query($total_query),0);

//check if user exists then login
if($total_results == 1)
{
//query the database for full details
$sql = "SELECT * FROM `logins` WHERE username = '".$_POST['username']."' AND password = '".$_POST['password']."'";
$query = @mysql_query($sql,$connection) or die(mysql_error());
while ($row = mysql_fetch_array($query))
{
//create sessions
session_register('loggedin'); //to confirm that the user has signed in
$_SESSION['loggedin'] = 'testingsystem'; //create code to confirm user has logged in
}
print "<meta http-equiv=\"refresh\" content=\"0;URL=main.php\">";
exit();
}
else
{
// wrong username and password
print "<script>alert('Incorrect username\password combination.');</script>";
print "<meta http-equiv=\"refresh\" content=\"0;URL=index.php\">";
}
}

 

The problem is that I need to check the username and password are case sensetive, is there anyway I can do that?

 

Thanks in advance

 

Ed

 

[[SOLVED] if statement in QUERY]

Thanks

[[SOLVED] if statement in QUERY]

Hi all

 

I have the following QUERY

 

SELECT * FROM news WHERE title LIKE '%".$_GET['q']."%' OR content LIKE '%".$_GET['q']."%' AND pageid != 3496

 

How can I mod this QUERY so that if pageid does = 3496 then it just returns the last 10 rows for any rows containing 3496 but still keep it returning *

 

Does that make sense?

 

Any help would be ace

 

Thanks

 

Ed

[[SOLVED] Storing Data For Shopping Carts - Advice Needed]

thanks

[[SOLVED] Storing Data For Shopping Carts - Advice Needed]

Hi everyone

 

I'm building a shopping cart for a friend and so far I am at a point where a customer ID number is generated when they first access the shop. To create this number I have done some random number generator and database timestamp to create this.

 

My question is, what do you think is the best way to store the products they are adding to their cart as they use it? Is it best to save the product they add to their cart in a database table along with the customer ID number, or is it best to do this all as sessions and don't insert any of the products they add until they have completed the payment page?

 

Personally I would store it in a database, but the database maybe quite big sometime in the future with old order information that was never purchased and therefore slow the system down.

 

What do you think?

 

Thanks

 

Ed

[Storing Product Information - ADVICE NEEDED]

and hold all the products and related categories in the category_associations table?

 

if so, how do you suggest I store this data in the category_associations table?

[Storing Product Information - ADVICE NEEDED]

Thanks Barand

 

But in the products table itself, should I store the category ID numbers in one field or have a field for main category, a field for a sub category and so on?

[Storing Product Information - ADVICE NEEDED]

Hi all

 

I need some advice, basically im building a shopping cart and I have this example as one my product lists

 

main category (cloths)

sub category (tshirts)

sub sub category (funny tshirts)

sub sub sub category (starwars)

sub sub sub sub category (R2D2)

 

I need to store what categories each product is assigned to.

 

Now I need some advice, should I have in my products table the following fields

 

maincat

subcat

subsubcat

subsubsubcat

subsubsubsubcat

 

and store the relevant category ID numbers in each field of those 5 fields?

 

Or should I just have one field and store the category ID numbers in one field, for example 2,34,234,543,788

 

Does that make sense?

 

Thanks

 

Ed

[[SOLVED] Query keeps returning NULL]

HAHA, yes im a plonker, the data didnt exist with the correct fields

 

Thanks

[[SOLVED] Query keeps returning NULL]

Hi all

 

I am doing the following query but it keeps returning null

 

SELECT 
p.id, p.memberid 
FROM 
members_friends m 
LEFT JOIN 
members_photos_albums p 
ON 
m.friendid = p.memberid 
WHERE 
m.memberid = 11

 

Can anyone see why this might be?

 

Thanks in advance

 

Dave

[[SOLVED] Query Issue (crossing tables)]

thanks guys, think I cracked it