SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard wheeler's Avatar
    Join Date
    Mar 2006
    Location
    Gold Coast, Australia
    Posts
    1,369
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Displaying kindof complex data

    hello,

    ive just hit one of those points where something seems to be logical but when you try and do it, it just wont work.

    Basically I want to display a table that fetches data from a couple of database tables.

    Row 1 will show me all the campaigns eg. campaign 1 | compaign 2 etc. This is one database table.

    The next row is the start of a loop that goes through all the user accounts (2nd database table). I want to display a checkbox that is ticked if that user is a member of the column campaign.

    The big problem seems to be achieving this without putting a query inside the loop. surely it can be achieved otherwise?

    here is my code:
    Code:
    <?php
    
    $query_users = mysql_query("SELECT u.id, u.email, s.user_id, s.campaign_id FROM ml_users u LEFT JOIN ml_subscriptions s ON u.id = s.user_id");
    $campaigns_row1 = mysql_query("SELECT * FROM ml_campaigns");
    
    ?>
    
    <div align="center">
    <h1>Manage Users</h1>
    <form id="update_subscriptions" name="update_subscriptions" method="post" action="">
    <table border="0" cellpadding="6" cellspacing="0" class="table-border">
      <tr>
        <td>Email</td>
    	<?php while ($c_data = mysql_fetch_array($campaigns_row1)) { 
    	$c_id = $c_data['id'];
    	$c_name = $c_data['name'];
    	$c_desc = $c_data['description'];
    	?>
        <td><?php echo $c_name; ?></td>
    	<?php } ?>
      </tr>
    <?php while ($user_data = mysql_fetch_array($query_users)) {
    
    $user_id = $user_data['id'];
    $email = $user_data['email'];
    $camp_id = $user_data['campaign_id'];
    $camp_user_id = $user_data['user_id'];
    
    ?>
      <tr>
        <td><?php echo $email; ?></td>
        <td><input type="checkbox" name="<?php echo $c_id; ?>" value="<?php echo $user_id; ?>" <?php
    	if ($camp_id == $c_id and $camp_user_id == $user_id) echo 'checked="checked"';
    	?> /><br />
    <?php echo "camp_id = $camp_id <br /> c_id = $c_id <br /> camp_user_id = $camp_user_id <br /> user_id = $user_id"; ?></td>
      </tr>
    <?php } ?>
    </table>
    </form>
    </div>
    I seem to be going around in circles trying to get this working! The problem at present is that the checkboxes only just up in the 1st campaign column, the 2nd and all subsequent columns are blank. has anyone tackled this type of output before?
    Studiotime - Time Management for Web Developers
    to-do's, messages, invoicing, reporting - 30 day free trial!
    Thomas Multimedia Web Development

  2. #2
    SitePoint Enthusiast mrsmiley's Avatar
    Join Date
    Jul 2004
    Location
    Melbourne
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your problem is more to do with the way you structured the code than anything else. There is also a problem with the way you have named the campaign checkboxes as there is no way to uniquely identify them.

    You also have the problem (which I think it more what you were originally getting at) where you aren't actually looping over the sub query on every line, or resetting the internal cursor on the recordset.

    Try this version:

    Code:
    <?php
    
    $query_users = mysql_query("SELECT u.id, u.email, s.user_id, s.campaign_id FROM ml_users u LEFT JOIN ml_subscriptions s ON u.id = s.user_id");
    $campaigns_row1 = mysql_query("SELECT * FROM ml_campaigns");
    
    ?>
    
    <div align="center">
    <h1>Manage Users</h1>
    <form id="update_subscriptions" name="update_subscriptions" method="post" action="">
    
    
    <table border="0" cellpadding="6" cellspacing="0" class="table-border">
      <tr>
        <td>Email</td>
    	<?php
    		while ($c_data = mysql_fetch_array($campaigns_row1))
    		{ 
    			$c_id = $c_data['id'];
    			$c_name = $c_data['name'];
    			$c_desc = $c_data['description'];
    		?>
    		<td><?php echo $c_name; ?></td>
    	<?php
    		}
    	?>
      </tr>
    <?php
    	while ($user_data = mysql_fetch_array($query_users))
    	{
    		$user_id = $user_data['id'];
    		$email = $user_data['email'];
    		$camp_id = $user_data['campaign_id'];
    		$camp_user_id = $user_data['user_id'];
    		?>
    		<tr>
    			<td><?php echo $email; ?></td>
    			<?php
    				mysql_data_seek($campaigns_row1, 0);
    
    				while ($c_data = mysql_fetch_array($campaigns_row1))
    				{
    					$c_id = $c_data['id'];
    				?>
    				<td><input type="checkbox" name="camp_<?php echo $c_id; ?>[]" value="<?php echo $user_id; ?>" <?php if ($camp_id == $c_id and $camp_user_id == $user_id) echo 'checked="checked"'; ?> /><br />
    		<?php echo "camp_id = $camp_id <br /> c_id = $c_id <br /> camp_user_id = $camp_user_id <br /> user_id = $user_id"; ?></td>
    				<?php
    				}
    			?>
    		</tr>
    <?php
    	}
    ?>
    </table>
    
    
    </form>
    </div>
    The results should come back in an array for each campaign like $_POST['camp_<id>'] where <id> is the campaign ID and the values in the array will be the users ID's.

  3. #3
    SitePoint Wizard wheeler's Avatar
    Join Date
    Mar 2006
    Location
    Gold Coast, Australia
    Posts
    1,369
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    awesome! thanks mrsmiley!

    I've never used msyql_data_seek before, part of my problem was I doubled up the campaign query because I wasn't aware you could reset the result like this - wat a nifty little thing that is.

    I knew I was going to have problems identifying the checkboxes after submission, I just hadn't got to that part of the problem yet.

    thanks again!
    Studiotime - Time Management for Web Developers
    to-do's, messages, invoicing, reporting - 30 day free trial!
    Thomas Multimedia Web Development


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •