Jim R

What? Where? When? : )

Define serious? The ability to add names to my database while I'm away from my laptop will save me a great deal of time in certain moments. I'll be in a gym late tonight, all day on Saturday and all day on Sunday. If at the end of my trip all my new names are tucked away in my database before I even leave the gym, when I get to the hotel or home, I just lay down and go to sleep. I'd just like to do it in spreadsheet form like I do with SQLyog.

Ok...that helped, thank you. But as expected, it's just noticing the last row of data. I need it to recognize any/all of the changes.

Hopefully I can explain this well. I have a list, which is linked here: http://hoosierhoopsreport.com/test-11-rank/ When I'm done, I'm trying to set up the list I, as ADMIN, can make changes to the list without having to go into my database. I'm mostly concerned with the # column, but once we get through this, I can apply what I've learned. I'm setting the form to print # and school as text fields. I'm getting id, nameFirst and nameLast as hidden values to pass to my form handler. When I hit submit, I'm getting this error: I'm pretty sure it's just filtering through the information to most last piece of data handled, which isn't what I want, but I also don't understand why I'm getting the error. Here is the code dealing with the form: <form id="class2011" action="/resources/form/dbplayerUpdate.php" method="post"> <table class="playerTable" border="0" width="100%"> <thead> <tr> <th class="num">#</th> <th class="top">Top 10</th> <th class="height">HT</th> <th class="pos">POS</th>'; if (current_user_can("access_s2member_level4")){ echo '<th class="level">Level</th>'; } echo '<th class="school">City (School)</th>'; if (current_user_can("access_s2member_level4")){ echo '<th class="summer">Summer Team</th>'; } echo '<th class="college">College</th> </tr> </thead> <tfoot> <tr> <td colspan="8" align="right">(+) moved up; (d) debut; (s) switched from another position / <b>Colleges in bold print means committed</b></td> </tr> <tr> <td><input id="Submit" name="Submit" type="submit" value="Submit" /></td> </tr> </tfoot> <tbody>';$query = 'SELECT * FROM wp_playerRank_copy WHERE year="2011" ORDER BY rankClass ASC'; $results = mysql_query($query); while($line = mysql_fetch_assoc($results)) { if ($line['rankClass'] != 0) { echo ' <tr> <td> <input type="hidden" name="db_id" value="' . $line['id'] . '"size="2"> <input type="text" name="rankClass" value="' . $line['rankClass'] . '"size="2">' . $line['devClass'] .'</td> <td>'; if ($line['wpID'] > '0') { echo ' <input type="hidden" name="nameFirst" value="' . $line['nameFirst'] . '"size="2"> <input type="hidden" name="nameLast" value="' . $line['nameLast'] . '"size="2"> <a href="/tag/' . $line['nameFirst'] . '-' . $line['nameLast'] .'">'. $line['nameFirst'] . ' ' . $line['nameLast'] . '</a>'; } else { echo $line['nameFirst'] . ' ' . $line['nameLast']; } echo '</td><td>'. $line['height'] . '</td> <td>'. $line['position'] . '</td>'; if (current_user_can("access_s2member_level4")){ echo '<td>'. $line['level'] . '</td>'; } echo '<td><input type="text" name="hschool" value="' . $line['hschool'] .'"size="30"></td>'; if (current_user_can("access_s2member_level4")){ echo '<td>'. $line['summer'] . '</td>'; } if ($line['committed'] == 'y') { echo ' <td><strong>'. $line['college'] . '</strong></td> ';} echo '</tr> ';} } echo ' </tbody></table></form> Here is the form handler: if(!$con = mysql_connect("localhost","jwrbloom_","redcoach")) { die("Could not connect to database: ".mysql_error()); } mysql_select_db("jwrbloom_wpHHR", $con); $nameFirst = $_POST['nameFirst']; $nameLast = $_POST['nameLast']; $db_id = $_POST['db_id']; $rankClass = $_POST['rankClass']; $hschool = $_POST['hschool']; //$grade = $_POST['grade']; //$coachSchool = $_POST['coachSchool']; //$feet = $_POST['feet']; //$inches = $_POST['inches']; /* search for existing row */ $sql = "SELECT id FROM wp_playerRank_copy WHERE nameFirst='".mysql_real_escape_string($nameFirst)."' AND nameLast='".mysql_real_escape_string($nameLast)." '"; if(!$result = mysql_query($sql)) { die(mysql_error()."<br />Query: ".$sql); } if(mysql_num_rows($result)) { $row = mysql_fetch_assoc($result); /* update existing row */ $sql = "UPDATE wp_playerRank_copy SET rankClass='".mysql_real_escape_string($rankClass)."', hschool='".mysql_real_escape_string($hschool)."', WHERE id='".$row['id']."'"; if(!$result = mysql_query($sql)) { die(mysql_error()."<br />Query: ".$sql); } }

Anyone have any experience with this? I can't seem to locate anything aside from viewers.

When it posts I don't have to worry about the name fields all being the same? From there I just need to associate the changed data to the static data (nameFirst, nameLast, id)?

I have a list that goes about 50 deep that I make changes to quite a bit. Each row has five columns, and the information sits on a database. Instead of making the changes in the database, I'd like to make the changes right on my site, so I can actually the list in order, then submit any changes. (It's just easier to look on my web page than the data table, and it's easier to grant the ability to change data on that page than give another data table access.) They are ordered by rankings 1-50. To do that, I'm picturing rows of data, printed in form elements instead of just printed statically on the page. From there I can make the changes I need (mostly to just the one column) then his Submit. Here is how the code looks now: <table class="playerTable" border="0" width="100%"> <thead> <tr> <th class="num">#</th> <th class="top">Top 10</th> <th class="height">HT</th> <th class="pos">POS</th>'; if (current_user_can("access_s2member_level4")){ echo '<th class="level">Level</th>'; } echo '<th class="school">City (School)</th>'; if (current_user_can("access_s2member_level4")){ echo '<th class="summer">Summer Team</th>'; } echo '<th class="college">College</th> </tr> </thead> <tfoot> <tr> <td colspan="8" align="right">(+) moved up; (d) debut; (s) switched from another position / <b>Colleges in bold print means committed</b></td> </tr> </tfoot> <tbody>';$query = 'SELECT * FROM wp_playerRank WHERE year="2011" ORDER BY rankClass ASC'; $results = mysql_query($query); while($line = mysql_fetch_assoc($results)) { if ($line['rankClass'] != 0) { echo ' <tr> <td>'. $line['rankClass'] . $line['devClass'] .'</td> <td>'; if ($line['wpID'] > '0') { echo '<a href="/tag/' . $line['nameFirst'] . '-' . $line['nameLast'] .'">'. $line['nameFirst'] . ' ' . $line['nameLast'] . '</a>'; } else { echo $line['nameFirst'] . ' ' . $line['nameLast']; } echo '</td><td>'. $line['height'] . '</td> <td>'. $line['position'] . '</td>'; I'm primarily interested in $line['rankClass'] being editable right on my web page. I"m seen syntax examples, but they have all been just changing one line at a time. I could theoretically make changes in all 50 rows. From a syntax perspective, I feel like I'm going to run into a NAME issue. Example: <input maxlength="5" name="rankClass" size="2" type="text" /> I realize I need to change "rankClass" to PHP that brings in the value from the data table. However, the PHP would also do that for 50 rows of data. Do I need to worry about duplicate instances being name="rankClass"? How would that look?

Yes. Here is what I'm needing to query. $member = 'SELECT * FROM wp_usermeta WHERE meta_value LIKE "%s2member_level%"'; $m_results = mysql_query($member); $custom = 'SELECT * FROM wp_usermeta WHERE meta_key = "wp_1_s2member_custom_fields"'; $c_results = mysql_query($custom); The columns are user_id, meta_key, meta_value. Keep in mind, what Joel put together worked perfectly, but as I went to implement it, I realized I didn't fully understand what I was needing. You might be able to use that as a guide. What he wrote provided a total numbers of Users live in each region (based on which county they live in). The number I need is among those, which ones are subscribers.

Hey, thanks for replying. This sort of flowed into a new problem. http://www.phpfreaks.com/forums/index.php?topic=331174.0

Probably not unions.

Union query? Learning more about it.

That works, but I just discovered an issue with the data I need. I need to also get WHERE meta_value LIKE '%s2member_level2%' . The count has to be where both those conditions are met with the matching user_id. This is all from the same table.

With the meta_value noted above. Here is the raw data: a:1:{s:6:"county";s:1:"1";} The "1" is the region. Basically, registrants choose what county they live in, and each county is assigned a number of 1-5.

Even if the "region" isn't really a column? I've never dealt with unserialized data before. That was a suggested earlier, and while it works, I'm trying to extend the information presented to my user.

I have about 300 users, and I'm trying to group them geographically according what part of the state their county is in OR if they're reading my site because they have a favorite college team. I've created a page that I want to show the total count for each group. There will be 13 groups, and I'd rather not have 13 queries. Basically, I think, I can't make heads or tails of nested loops. I'd like it to look like this: Region 1 (## members) Region 2 (## members) Region 3 (## members) etc. ... ... ... ... ... I assume there will be IF and ElseIF statements. Here is what I'm starting with, but I can't even get the first group working: $query = 'SELECT * FROM wp_usermeta WHERE meta_key = "wp_1_s2member_custom_fields"'; $results = mysql_query($query); while($line = mysql_fetch_assoc($results)) { $meta_value = unserialize($line['meta_value']); echo '<div>Region 1 (' . $d1 . ' Users)</div>'; if ($meta_value['county'] == '1' || $meta_value['county'] == '2') { ++$d1; } };

Ok...I'm trying to get it to count the number of relevant items, display the User ID's then show the total number that match. $query = 'SELECT * FROM wp_usermeta WHERE meta_key = "wp_1_s2member_custom_fields"'; $results = mysql_query($query); while($line = mysql_fetch_assoc($results)) { $meta_value = unserialize($line['meta_value']); if ($meta_value['county'] == '1' || $meta_value['county'] == '2') { $count1=0; foreach ($line as $user) { $count1=$count1+1; echo $user['user_id'] . '<br> '; } echo $count1; } } That code produced: 1 4 w a 41 4 w a 4 It should show: 439 440 2

I haven't read what Muddy linked yet, but it might answer this question. Does the *if*....$line['meta_value'] work if I'm just trying to get part of the data in that field? Here is the raw data: a:1:{s:6:"county";s:1:"4";} From that, I'm going to look for "county" and then figure out if the "#" at the end is 1,2,3,4, or 5.

Here is where I'm starting: SELECT * FROM wp_usermeta WHERE meta_key = 'wp_1_s2member_custom_fields' AND meta_value LIKE '%3%' As the PHP produces the page, I'm going to have 15 different options coming from the meta_value LIKE part. I'm assuming I can set up my 15 different DIVs, each with their own IF statement involving the meta_value. Right? I'm not going to have to have 15 different queries, am I? I assume it would start like this: $query = 'SELECT * FROM wp_usermeta WHERE meta_key = "wp_1_s2member_custom_fields"'; $results = mysql_query($query); while($line = mysql_fetch_assoc($results)) But from there, I don't know if it's possible to deal with partial data with IF statements.

The column in question, is basically mirroring WordPress category tag ID's, so I could likely copy them over with another query. Right?

Here is the query: UPDATE wp_playerRank SET wpID = REPLACE(wpID,'0','(NULL)'); It didn't do what I wanted, and it basically got rid of every zero, not just lone 0's. So 207 became 27.

I ran a Replace query in my database that didn't produce the results I wanted, not in the least. Is there a way I can undo it???

It might be called object mapping, but I'm not sure. Basically, I'm wanting somewhat of a GUI where the User clicks on an area of the page, and the location of the click is noted (for a database). From there it can be given a value based on whether or not a right click was used vs a left click.

I've tried it without the limit, and it works fine. I use that query elsewhere too without the limit. I just added the limit because I'm reproducing a smaller list on my homepage.

OK...I put it at the end, and while it eliminated the error, I'm not getting any results now. $query = 'SELECT * FROM wp_playerRank WHERE year="2011" ORDER BY rankClass ASC LIMIT 5';