Complex variable splitting question

parolin

This contains some mySql but revolves around basic PHP functions, so I decided to post it here.

Hey, I know this may sound like a question that can be found anywhere on this site, but I assure you I have been searching all over the forums and cannot find a solution for my unique case.

What I am trying to create is a script that can bring queries from two different mySql tables togtether to create a page. However, the queries are customized by the poster. For example, he may want to display the 2nd and third entries of one table and the 4th and 20th entries from the other table.

My idea was to have his selections for each query be represented by a two-character combination (ie. S1, M3, or S6). The S and the M would designate which mySql table to pull the query from as the number that followed it would designate which entry to pull from the specified table. Still with me? I hope so.

Alright, so far we know that the poster has designated what entries he would like to pull from two different mySql tables. Now the hard part in my mind is how to go about querying these and how to display them? I have thought about the 'explode', 'implode', and every other kind of -plode, but I cannot come up with a logical solution.

Coding whise, here is what I think I need:

$userpostedvariable = "S1 M2 M4 S5 M4"; // The user inputted variable

1) I need explode the variable and need to count each two-character combination (ie. S1 or M4) to know how many there are in the variable.

2) From there I need to take all the two-character combinations beginning with 'S' and to pull them from the table where the 'id' number would be the following number after the 'S' (ie. S4 <--- 4 would be the ID number).

3) Do the same for all of the combinations beginning with 'M', and pull them from the other table.

4) Display the queries from both tables in the designated order specified in the user-inputted variable (ie. $userpostedvariable = "S1 M2 M4 S5 M4"; )

I am aware of how complex this sounds but if you could offer me any headway or a new (preferablly easier) direction to go about this, I would be forever in debt to you. Thank you so much!

josh parolin

Shrike

Ok sounds easy enough. First of all I would not submit a long string and then try to parse it, I would create arrays keyed with the table name, e.g.

<input type="checkbox" name="S[]" value="1"/>
<input type="checkbox" name="S[]" value="2"/>
<input type="checkbox" name="M[]" value="1"/>
<input type="checkbox" name="M[]" value="2"/>

Then when the form is submitted (assuming it is a form), you would get the following PHP POST data

$_POST = array (
  ['S'] = array (
    1,
    2
  ),
  ['M'] = array (
    1,
    2
  )
);

Then it's a matter of building the query from the data:

// Map our database table names here
$tabledefs = array (
  'S' => 'table1',
  'M' => 'table2',
  'L' => 'table3'
);

// Begin the query
$qry = "SELECT stuff FROM ". implode(", ", $tabledefs) . " WHERE ";
// Iterate over the POST data
foreach($_POST as $key => $value)
{
  // Check if key is OK to use
  if(array_key_exists($key, $tabledefs))
  {
    // Assumes we are selecting the same column name in each table
    foreach($value as $v)
    {
      $matches[] = $tabledefs[$key] . ".columnname = " . $v;
    }
  }
}
// Add the match portion to the query
$qry .= implode(" OR ", $matches);

// See if it worked : )
echo $qry;

If your using GET data you'd need to find a new way of passing the data.

parolin

First of all, thank you very much for the reply. I appreciate it alot. The code you provided gave me a base and a direction to work towards. However, I am still unsure of some things and I'll try to explain to you better what I am hoping to do now.

First, I have a database (jolt) with two tables (matches & segments). Now, the entries can be added through a form to the tables by anybody. That is the easy part because it is just inserting information into the database, so I already have that taken care of. Now, the administrator has a webpage where he can view all of the submitted entries from the two tables (matches and segments), which again is easy because all it calls for is withdrawing the information for viewing.

Now, the part I am stuck on and that you began helping me with last post was how, for the adminstrator, to select which segments and matches he wanted to show on a single webpage and in which order he wanted all of them. This is best done, I beleive, by your method of the check boxes and a simple form POST process as you provided.

So, the first thing we need is for a process that will create a checkbox for each entry in the tables. For example:


<form method="POST" action="">

Select which matches to post
<p><input type="checkbox" name="M[]" value="1"/> 
Match 1 </p>
<p><input type="checkbox" name="M[]" value="2"/> 
Match 2</p>

<p></p>

<p>Select which segments to post</p>
<p><input type="checkbox" name="S[]1" value="1"/> 
Segment 1</p>
<p><input type="checkbox" name="S[]" value="2"/> 
Segment 2</p>

<p></p>

<p><input type="submit" value="Submit" name="posting"></p>


</form>

However, the checkboxes need to be added for each record in each table with the WHILE function or another function, I believe. So, for the top matches section of the form, there would be a WHILE process combined with a mySQL query to gather all the entries from the table MATCHES and create checkboxes for them. Then, the same would be done on the bottom half for the segments portion, only it would take all the entries from the table SEGMENTS.

Alright, so now we would have the form completed. But, how about actually pulling querying the specified tables for the specified entries? You posted a code last post that seems like it would work with some modification.

Try to envision this scenario: The administrator selects the first check box in the 'matches' section of the form and the first check box in the 'segments' portion of the form. So, he wants to withdraw all of the fields from the table MATCHES where ID=1. He would like the same withdrawn from the table SEGMENTS where ID=1. Sort of get it?

Then all the variables passed for those two entries would be echo'd. I think I've asked enough, so maybe if we get this figured out I'll ask then how to put it in order.

But for now:

1. How to automate a process so to create checkboxes for each each entry in the two tables (as displayed above).

Have the query pull all of the variables out from the two tables where the ID equals what the user selected through the check boxes.
I (maybe) give you about $1,000,000,000 for helping me. (o:[/b]

I know I ask alot, but it would really mean alot to me if anyone could further help me with this. Thank you and enjoy the rest of your weekend.

josh parolin

parolin

Alright, I was able to use the WHILE function to make the checkboxes. So Step 1 is completed. Now I need help with the latter. /o:

(RESOLVED!) 1. How to automate a process so to create checkboxes for each each entry in the two tables (as displayed above).

Have the query pull all of the variables out from the two tables where the ID equals what the user selected through the check boxes (read previous post.)
I (maybe) give you about $1,000,000,000 for helping me. (o:
[/b]

<?php 

// The code we need to work with


if ($posting) {


// Map our database table names here 
$tabledefs = array ( 

  'M' => 'matches',
  'S' => 'segments' 

); 

// Begin the query 
$qry = "SELECT ID, MATCHTYPE, CONTENDER1, CONTENDER2
	FROM ". implode(", ", $tabledefs) . " WHERE "; 
// Iterate over the POST data 
foreach($_POST as $key => $value) 
{ 
  // Check if key is OK to use 
  if(array_key_exists($key, $tabledefs)) 
  { 
    // Assumes we are selecting the same column name in each table 
    foreach($value as $v) 
    { 
      $matches[] = $tabledefs[$key] . ".columnname = " . $v; 
    } 
  } 
} 
// Add the match portion to the query 
$qry .= implode(" OR ", $matches); 

// See if it worked : ) 
echo $qry;




} else {



// The form displayed if no form has yet been submitted


echo "<html><head><title>Post</title></head>
	<body>

<form method=\"POST\" action=\"\">
<p>Select which matches to post</p> ";




// The Matches Checkboxes


$connection = mysql_connect("localhost","root","") or die ("Couldn't connect to server.");
$db = mysql_select_db("jolt", $connection) or die ("Couldn't connect to server.");
$sql = "SELECT ID, MATCHTYPE, CONTENDER1, CONTENDER2
	FROM MATCHES
	ORDER BY ID DESC";
$sql_result = mysql_query($sql,$connection) or die ("Couldn't execute query.");

while ($row = mysql_fetch_array($sql_result)) {
	$id = $row["ID"];
	$matchtype = $row["MATCHTYPE"];
	$contender1 = $row["CONTENDER1"];
	$contender2 = $row["CONTENDER2"];

echo "<p><input type=\"checkbox\" name=\"M[]\" value=\"$id\">$matchtype: $contender1 vs. $contender2</p>";

}

mysql_free_result($sql_result);
mysql_close($connection);




// The Segments Checkboxes

echo "<p></p><p></p>

<p>Select which segments to post</p>


<p>!!!!!!!!!!!!!The SAME PROCESS FOR THE MATCHES WILL GO HERE!!!!!!!!!!!!!</p>

<p><input type=\"submit\" value=\"Submit\" name=\"posting\"></p>


</form></body></html>";



}


?>

Roger_Ramjet

How are you going to determine the 'order' that the user wants? If it is checkboxes then they will just be present as a value, not with any ordering indicated.

parolin

I'll worry about that after I find out how to pull all the information out of the tables correctly. Most likely I'll just create a new field called "orderid" and have the query sort Descending or Ascending based on the id.

Can anyone help me with the 2nd step though?