CODEWITHSUNDEEP
phpmysqlforeach
user1104854
user1104854

Reputation: 2167

sql using LIKE clause : php

I'm trying to generate a list of events that a user is attending. All I'm trying to do is search through columns and comparing the userid to the names stored in each column using LIKE.

Right now I have two different events stored in my database for testing, each with a unique eventID. The userid i'm signed in with is attending both of these events, however it's only displaying the eventID1 twice instead of eventID1 and eventID2.

The usernames are stored in a column called acceptedInvites separated by "~". So right now it shows "1~2" for the userid's attending. Can I just use %like% to pull these events?

$userid = $_SESSION['userid'];  
echo "<h2>My Events</h2>";
    $myEvents = mysql_query("select eventID from events where acceptedInvites LIKE '%$userid%' ");
    $fetch = mysql_fetch_array($myEvents);
        foreach($fetch as $eventsAttending){
            echo $eventsAttending['eventID'];
            }

My output is just 11 when it should be 12

Upvotes: 0

Views: 83

Answers (3)

Frederick Marcoux
Frederick Marcoux

Reputation: 2223

Instead of using foreach(), use while() like this:

$myEvents = mysql_query("SELECT `eventID` FROM `events` WHERE `acceptedInvites` LIKE '".$userid."'");
while ($fetch = mysql_fetch_array($myEvents))
{
    echo $fetch['eventID'];
}

It will create a loop like foreach() but simpler...

P.S. When you make a MySQL Query, use backticks [ ` ] to ensure that the string is not confused with MySQL functions (LIKE,SELECT, etc.).

Upvotes: 1

Ted Avery
Ted Avery

Reputation: 5689

I'm a bit confused by your description, but I think the issue is that mysql_fetch_array just returns one row at a time and your code is currently set up in a way that seems to assume $fetch is filled with an array of all the results. You need to continuously be calling mysql_fetch_array for that to happen.

Instead of

$fetch = mysql_fetch_array($myEvents);
foreach($fetch as $eventsAttending){
            echo $eventsAttending['eventID'];
            }

You could have

while ($row = mysql_fetch_array($myEvents)) {
   echo $row['eventID'];
}

This would cycle through the various rows of events in the table.

Upvotes: 2

Joe
Joe

Reputation: 15802

Change your table setup, into a many-to-many setup (many users can attend one event, and one user can attend many events):

users
- id (pk, ai)
- name
- embarrassing_personal_habits

events
- id (pk, ai)
- location
- start_time

users_to_events
- user_id  ]-|
             |- Joint pk
- event id ]-|

Now you just use joins:

SELECT u.*
  FROM users u
  JOIN users_to_events u2e
    ON u.id = u2e.id
  JOIN events e
    ON u2e.event_id = e.id
 WHERE u.id = 11

Upvotes: 3

Related Questions