Jim R

@mac_gyver your prompt made me check my connection, and I had the connection commented out.

I cleaned up the SELECT. I'm not getting any output out of that query on to my page, but it does work in my database software (Sequel Pro). It should be printing 309 rows. I'm not getting any errors, and I don't see anything wrong with my loop. I'm clearly missing something that my brain is blocking out.

The code is meant to print a schedule of games. Eventually, it will produce games on the given day, but I'm just trying to get it to list any game I have the database. I'm not getting any results. This is a stripped down version of code I'm using on another part of my site that involves listing players from each team, player stats and team records. It works just fine. Not sure what I'm missing here. $query = " SELECT *,s.id as sid, gamedate, s.home_id, s.away_id,h.id as hid, a.id as aid, (CASE WHEN h.toggle = 1 THEN concat(h.city,' ',h.school) WHEN h.toggle IS NULL THEN h.school ELSE concat(h.school,' (',h.toggle,')') END) as home, (CASE WHEN a.toggle = 1 THEN concat(a.city,' ',a.school) WHEN a.toggle IS NULL THEN a.school ELSE concat(a.school,' (',a.toggle,')') END) as away FROM a_schedule s JOIN a_schools h ON h.id = s.home_id JOIN a_schools a ON a.id = s.away_id "; $schools = mysqli_query($con,$query); echo mysqli_error($con); while($row = mysqli_fetch_assoc($schools)) { echo $row['away']. ' at ' .$row['home']; } a_schedule: a_schools:

Oh...one thing... Not everyone will have a County entry. There is a level of registration that is for college coaches. I didn't think about that, until I saw the results. Otherwise it cleaned up my NULL entries. EDIT: So I made the sub_region a LEFT JOIN, and it worked.

The use of meta_key for the sub level is because there are a couple of other rows with s2member entries for each User. There is just one row for each User with the county data. The county data is actually a custom entry I put in the User registration form. I did start using JOIN first, but I didn't remember (or think of) using aliases. I'll give this a try...thank you.

If you're familiar with WordPress, each user_id produces about 15-20 rows of usermeta data. I just want to get a couple of them, but the two rows for a User have serialized data. I'm looking to producing a list of Users who subscribe to a certain level -- sub_level -- and which part of Indiana they're from -- sub_region: North(1), Central(2) or South(3). Truly just interested in the s2member_ value in sub_level and the # (1,2 or 3) in sub_region, so there is likely a better way to get just that than LIKE, but I can code it how I want in the PHP output. I'm wanting only the rows of Users where sub_level isn't NULL. select u.id, u.user_login, u.user_email, u.user_registered, (select um1.meta_value from wp_usermeta um1 where u.id = um1.user_id and um1.meta_value LIKE concat ('%', 's2member_', '%') and um1.meta_key = 'wp_capabilities') as sub_level, (select meta_value from wp_usermeta um2 where u.id = um2.user_id and um2.meta_value LIKE concat ('%', 'county', '%')) as sub_region from wp_users u Table structures: wp_usermeta: wp_users: Output looks like this: I tried 'WHERE sub_level is not null', but as you know, can't use an alias in a WHERE. I tried 'WHERE um1.meta_value is not null' in the subquery, but that didn't change the results. I also tried it in the outer query, but I got an unknown column error..

After I got most of the descriptors to lineup, it only pulled one row. It should've pulled two for now. Ultimately, it will pull in however many set up on each date we post. Also, the problem on the grade is their grade has to remain relative to the date of the game. Ultimately, I'll be adding variables to the query, referencing the date of the games. I'd think I can remedy this with a CASE statement.

Thanks...I'll play around with it. I still can't wrap my head around subqueries -- when to use them, where to use them, how to use them. Disappointed I was that far off.

The root of my issue, how to break up the hplayers and aplayers strings so I can print the player lists how I'd like them? I guess the side question goes toward what you said earlier, how creating separate tables for position and grade change what's in the hplayers and aplayers fields?

Added this: $results = mysqli_query($con,$query); while($line = mysqli_fetch_assoc($results)) { $lines = explode(PHP_EOL,$line['hplayers']); $array = array(); foreach ($lines as $player) { $array[] = str_getcsv($player); } print_r($array); Producing this: I tried echoing $array[0], but it just produced 'array'.

Ultimately, it seems, the results would still end up in hplayers or aplayers. How would I otherwise get those two columns linked to each player?

With commas coded in: Logan Imes,6,4,1,23;Nick Richart,6,8,5,23;Isaiah Davis,6,8,5,22;Edward Thomas,6,4,2,23 I could concatenate the whole thing, but it would still be, Logan Imes, 6'4", 1, 23. How then to get the 1 = point guard and the 23 = JR.

Yes, ultimately grade > '21' will be grade > $season. I'm trying to get the desired results for right this moment. I'm just struggling with turning the data in the field... ...into something more palatable onscreen. Would coding commas into the Concat help?

I could for position, as they could never change, but normally I use this: $position = array ( '1' =>'Point Guard', '2' =>'Shooting Guard', '3' =>'Small Forward', '4' =>'Power Forward', '5' =>'Center' ); The grades change each year relative to class, and I have a Seasons table that I call to in each function I need it, taking the date of the post, then I use CASE to determine the grade. The problem I see is separating the values in the output. Adding separators would allow me it explode it. Could I then use it that way? The only other times I've exploded a list is when it just came from one column.

...or if there is a better way to get the data. Creating game capsules for basketball games. They are essentially previews of upcoming games. They will include the team name and 'key' players. I'm just now getting to the output, and I feel like my query is getting me the information I need from it. School names, player names, player height (feet, inches), position he plays and what grade he is in. I could just CONCAT that all out, but I really want to convert the numerical values for position and grade into words rather than numbers. Position: 1 = point guard, 2 = shooting guard, etc Grade: 22 = senior, 23 = junior, etc Normally, I know how to do that when I'm pulling non-array data, even when it's an array. I'm not sure, however, if I'm creating an array. $query="SELECT h.school as home, a.school as away, preview, group_concat( distinct CONCAT(hp.nameFirst,' ',hp.nameLast),hp.feet,hp.inches,hp.position,hp.grade SEPARATOR ';' ) as hplayers, group_concat( distinct CONCAT(ap.nameFirst,' ',ap.nameLast),ap.feet,ap.inches,ap.position,ap.grade SEPARATOR ';' ) as aplayers FROM a_schedule sc LEFT JOIN a_schools h on sc.home_id = h.id LEFT JOIN a_schools a on sc.away_id = a.id LEFT JOIN a_players hp on h.id = hp.schoolID and hp.grade >'21' and hp.key='1' LEFT JOIN a_players ap on a.id = ap.schoolID and ap.grade >'21' and ap.key='1' Group by sc.id order by sc.id "; $results = mysqli_query($con,$query); while($line = mysqli_fetch_assoc($results)) { echo $line['hplayers']; } This is what the output looks like in my query: Ultimately, it will read on screen: Dayveon Turner, 6'0" point guard; SR Reggie Bass, 6'4" shooting guard; SR Antonio Lisenbee, 6'6" power forward; SR Do I fetch array? Table structures: a_schools a_schedule a_players (id column too)