PHP - Selecting Multiple Values From A Field

I am running a query to get a certain student's quiz results from all attempts and print one after the other in a simple table for now. The student is designated by an email address. I need results from all of the rows containing this particular email. The issue I think I am having is that when I use "WHERE" and specify an email address that exists many times in the table it isn't storing the variable correctly. Here is code below that I cannot get working right.

When I try to print these variables as a test to see if they are working I get nothing. No errors either. I have checked all names, spellings, and capitalization about 5 times. Thanks for looking at it.

Code: [Select]
$QuizQuery = mysql_query("SELECT * FROM Quiz_Results WHERE Email = '".$_SESSION['UserEmail']."' AND Quiz_Name = 'M1'")
or die(mysql_error());

$i=1;

while ($QuizResults = mysql_fetch_array($QuizQuery)){

$UserEmail = $QuizResults['Email'];
$Score = $QuizResults['Score'];
$Date = $QuizResults['Date_Taken'];


echo "<table width='650' border='1'><tr><td>";
echo "Attempt #".$i."<br></td><td>Sco ".$Score."</td><td>";
if($QuizResults['Pass'] == "PASSED"){
echo "Passed";
}
else {
echo "Failed";
}
echo "</td><td>Date Taken: ".$Date."</td></tr></table>";



$i++;
}

i am making a comparison site for products

say i have a table
                     !        A       !        B            !        C       !
----------------------------------------------------------------------
   product 1   !     1.34      !       2.78      !         7.90    !
----------------------------------------------------------------------
   product 2   !     1.56      !       3.90       !       8.00    !
----------------------------------------------------------------------
   product 3   !     1.22      !       2.99       !     9.00       !
----------------------------------------------------------------------
   product 4   !      1.09     !       3.00       !      8.50      !
----------------------------------------------------------------------

say i wanted to buy product 1 and 2 from the cheapest company A/B or C

how would i go about doing this ?

any guidance would be helpful  cheers matt

Hi,

My company has 240+ locations and as such some users (general managers) cover multiple sites.  When I run a query to pull user information, when the user has multiple sites to his or her name, its adds the second / third sites to the next columns, rather than wrapping it inside the same table cell.  It also works the opposite way, if a piece of data is missing in the database and is blank, its pull the following columns in.  Both cases mess up the table and formatting.  I'm extremely new to any kind of programming and maybe this isn't the forum for this question but figured I'd give it a chance since I'm stuck.

The HTML/PHP code is below:

<table id="datatables-column-search-select-inputs" class="table table-striped" style="width:100%">

                                        <thead>

                                            <tr>

                                                <th>ID</th>

                                                <th>FirstName</th>

                                                <th>LastName</th>

                                                <th>Username</th>

                                                <th>Phone #</th>

                                                <th>Location</th>

                                                <th>Title</th>

                                                <th>Role</th>

                                                <th>Actions</th>

                                            </tr>

                                        </thead>

                                        <tbody>

                                            <?php

                                            //QUERY TO SELECT ALL USERS FROM DATABASE

                                            $query = "SELECT * FROM users";

                                            $select_users = mysqli_query($connection,$query);

                                            

                                            // SET VARIABLE TO ARRAY FROM QUERY

                                            while($row = mysqli_fetch_assoc($select_users)) {

                                                $user_id = $row['user_id'];

                                                $user_firstname = $row['user_firstname'];

                                                $user_lastname = $row['user_lastname'];

                                                $username = $row['username'];

                                                $user_phone = $row['user_phone'];

                                                $user_image = $row['user_image'];

                                                $user_title_id = $row['user_title_id'];

                                                $user_role_id = $row['user_role_id'];

                                            

                                                // POPULATES DATA INTO THE TABLE

                                                echo "<tr>";

                                                echo "<td>{$user_id}</td>";

                                                echo "<td>{$user_firstname}</td>";

                                                echo "<td>{$user_lastname}</td>";

                                                echo "<td>{$username}</td>";

                                                echo "<td>{$user_phone}</td>";

                                            

                                                //PULL SITE STATUS BASED ON SITE STATUS ID

                                                $query = "SELECT * FROM sites WHERE site_manager_id = {$user_id} ";

                                                $select_site = mysqli_query($connection, $query);

                                                while($row = mysqli_fetch_assoc($select_site)) {

                                                    $site_name = $row['site_name'];

                                                    echo "<td>{$site_name}</td>";

                                                }

                                                echo "<td>{$user_title_id}</td>";

                                                echo "<td>{$user_role_id}</td>";

                                                echo "<td class='table-action'>

                                                    <a href='#'><i class='align-middle' data-feather='edit-2'></i></a>

                                                    <a href='#'><i class='align-middle' data-feather='trash'></i></a>

                                                    </td>";

                                                //echo "<td><a href='users.php?source=edit_user&p_id={$user_id}'>Edit</a></td>";

                                                echo "</tr>";

                                            }   

                                            ?>

 

                                            <tr>

                                                <td>ID</td>

                                                <td>FirstName</td>

                                                <td>LastName</td>

                                                <td>Username</td>

                                                <td>Phone #</td>

                                                <td>Location</td>

                                                <td>Title</td>

                                                <td>Role</td>

                                                <td class="table-action">

                                                <a href="#"><i class="align-middle" data-feather="edit-2"></i></a>

                                                <a href="#"><i class="align-middle" data-feather="trash"></i></a>

                                                </td>

                                            </tr>

                                        </tbody>

                                        <tfoot>

                                            <tr>

                                                <th>ID</th>

                                                <th>FirstName</th>

                                                <th>LastName</th>

                                                <th>Username</th>

                                                <th>Phone #</th>

                                                <th>Location</th>

                                                <th>Title</th>

                                                <th>Role</th>

                                            </tr>

                                        </tfoot>

                                    </table>

Hi All ,   I have a small table with 4 fields namely Day_ID, Dues, Last_Visit, Points. where Day_ID is an auto-increment field. The table would be as follows:   Day_ID -- Dues ---  Last_Visit ---  Points. 1  --------- 900 -------- 1/12  --------   6      2  --------- 700 -------- 4/12  --------   7 3  --------- 600 -------- 7/12  --------   5 4  --------- 600 -------- 9/12  --------   6 5  --------- 600 -------- 10/12 -------   6 6  --------- 600 -------- 14/12 -------   6   So this is the record of a person's visit to say a club. The last row indicates the last date of his visit to the club. His points on this date are 6. Based on this point value of 6 in the last row I want to retrieve all the previous BUT adjoining all records that have the same Points i.e. 6.    So my query should retrieve for me, based on the column value of Points of the last row (i.e. Day_ID - 6 ), as follows:   4  --------- 600 -------- 9/12  --------   6 5  --------- 600 -------- 10/12 -------   6 6  --------- 600 -------- 14/12 -------   6   This problem stated above had been completely resolved, thanks to a lot of help from Guru Barand by this following query :-

$query = "SELECT cv.day_id, cv.dues, cv.last_visit, cv.points
FROM clubvisit cv
WHERE last_visit >=
(
SELECT MAX(last_visit) FROM clubvisit
WHERE points <>
(
SELECT points as lastpoints
FROM clubvisit
JOIN
(
SELECT MAX(last_visit) as last_visit
FROM clubvisit
) as latest USING (last_visit)
)
)";

I am using this and it works perfectly except that now there is a slight change in the table because the criteria for points is now dependent on more than one column cv.points and is more like cv.points1, cv.points2, cv.points3 etc. So now I need to make a selection based on each of these cv.points columns.   As of now I can still get the results by running the query multiple times for each of the cv.points columns ( seperately for cv.points1, cv.points2, cv.points3) and it works correctly. However I am wondering if there is a better way to do this in just one go. This not only makes the code repetitive but also since the queries are interconnected, involves the use of transactions which I wish to avoid if possible.    The values that I require for each of the cv.point columns is  1. day_id of the previous / old day on which the cv.points value changed from the current day value, and  2. cv.points on that old/ previous day.    So for  example if the table is as below:   Day_ID -- Dues ---  Last_Visit ---  Points1 --- Points2. 1  --------- 900 -------- 1/12  -----------   9  ------------ 5    2  --------- 600 -------- 4/12  -----------   6  ------------ 6 3  --------- 400 -------- 7/12  -----------   4  ------------ 7 4  --------- 500 -------- 9/12  -----------   5  ------------ 8 5  --------- 600 -------- 10/12 ----------   6  ------------ 8 6  --------- 600 -------- 11/12 ----------   6  ------------ 8 7  --------- 600 -------- 13/12 ----------   6  ------------ 7 8  --------- 500 -------- 15/12 ----------   5  ------------ 7 9  --------- 500 -------- 19/12 ----------   5  ------------ 7   Then I need the following set of values : 1. day_id1 -- Day 7,      points1 ---- 6,          days_diff1 -- (9-7 = 2)  . // Difference between the latest day and day_id1 2. day_id2 -- Day 6,      points2 ---- 8,          days_diff2  -- (9-6 = 3) 3. day_id3 -- ....   and so on for other points.    Thanks all !

I connected to a database.  i want to know how i can get the value out of that field and add it to the values of another.  i don't want to make any changes to the database.  i just want to use the values for coding.  please, if anyone knows how to do this, let me know.

I am having trouble sending information that was entered into a form, on the same page to the next page.

I am sure i have done this a few years ago but cant find the code and cant find the answer online, hope someone can help me.

My code is as follows:

<form method="POST" action="https://.............. .asp ">
<table>
  <tr>
    <td>Name: </td>
    <td><input type="text" name="clientname" /></td>
  </tr>
  <tr>
    <td>E-mail:</td>
    <td><input type="text" name="email" /></td>
  </tr>
  <tr>
    <td>Contact No:</td>
    <td><input type="text" name="telno" /></td>
  </tr>
</table>
<?php
$udate = date("y-m-d H:i:s", strtotime ("+2 hour"));
$uniquevalue = "$udate" . " " . "Simpay Pro";
echo $uniquevalue;
?>
<br />
<input type="hidden" name="info1" value="<?php print $uniquevalue?>">
<input type="hidden" name="info2" value=$clientname>
<input type="submit" value="Submit">
</form>

So basically info1 is working i can see the value on the next page after its submitted, but info2 is the value of the input field.

Is this possible to do it like this, or do i have to send it to another page and then to my last page that displays the values?

hey guys, i have my code set up to run substrings through a db to check for matches...even when there is a match it wont insert correctly
Code: [Select]
$result = mysql_query("select * from friends where userid=$userid");
while($row = mysql_fetch_assoc($result))  {

$first_name = $row['first_name'];
$last_name = $row['last_name'];
$strpos_1 = strpos($friend_1, ' ');
$substr_1 = substr($friend_1, 0, $strpos_1);
$substr_2 = substr($friend_1, $strpos_1);

if($substr_1 == $first_name && $substr_2 == $last_name) {

$sql = "insert into fav_friends values('', '$userid', '$friend_1', '$friend_2', '$friend_3', '$friend_4', '$friend_5', '$friend_6')";
          $query = mysql_query($sql);
        }
}
and i don't receive any errors, any thoughts?

I'm trying to add ui autocomplete functionality to some fields in a form and am running into a few issues.
The first is I want to be able to both add and pull multiple values from the one field. I had originally thought I could comma delimit the values and handle it that way, but it seems it's a bit more complicated than that. I've tried to explode the value but it doesn't go far enough, I think I need to find a way to refine the array down by trying to replace the comma in the code somehow.

Here's my current php code:

<?php

//connection information
$host = "localhost";
$user = "myuser";
$password = "mypass";
$database = "mydb";
$param = $_GET["term"];

//make connection
$server = mysql_connect($host, $user, $password);
$connection = mysql_select_db($database, $server);

//query the database
$query = mysql_query("SELECT cb_activities FROM jos_comprofiler WHERE cb_activities REGEXP '^$param'");

//build array of results
for ($x = 0, $numrows = mysql_num_rows($query); $x < $numrows; $x++) {
$row = mysql_fetch_assoc($query);
$activities[$x] = array(cb_activitiesterm => $row[cb_activities]);
}

//echo JSON to page
$response = $_GET["callback"] . "(" . json_encode($activities) . ")";
echo $response;

mysql_close($server);

?>


Someone else had mentioned maybe using something like this:

$string="([{\"cb_activitiesterm\":\"Kicking Cats, Working\"},{\"cb_activitiesterm\":\"Web Development\"}])";

$one=str_replace("([{\"cb_activitiesterm\":\"","",$string);
$two=str_replace("\"},{\"cb_activitiesterm\":\"",",",$one);
$three=str_replace("\"}])","",$two);

echo $three;

// now you can explode it ... by commas
$items=explode(",",$three); 

But of course the problem here is he's creating the string in the php file and adding the /'s when in reality this needs to be pulled from the database and then refined.

The values are currently formatted like this:
([{"cb_activitiesterm":"Kicking Cats, Working"},{"cb_activitiesterm":"Web Development"}])
Exploding simply adds a "" around the comma so it won't work.
I need it to format like this:
([{"cb_activitiesterm":"Kicking Cats"},{"cb_activitiesterm":"Working"},{"cb_activitiesterm":"Web Development"}])

My second issue is I need to be able to pass multiple values into the hidden input in the script and adding a comma/space between each value. Currently if you click another value it simply replaces the prior value with the next one.
Here's my script:
Code: [Select]
<div id="editcbprofile">
<div id="formWrap">
<div id="activities" class="ui-helper-clearfix">
<input id="cb_activitiesterm" type="text">
<input id="cb_activities" name="cb_activities" type="hidden">
</div>
</div></div>
<script type="text/javascript" src="autocomplete/js/jquery-1.4.2.min.js"></script>
<script type="text/javascript" src="autocomplete/js/jquery-ui-1.8.custom.min.js"></script>
<script type="text/javascript">
$(function(){

//attach autocomplete
$("#cb_activitiesterm").autocomplete({

//define callback to format results
source: function(req, add){

//pass request to server
$.getJSON("autocomplete/autocom.php?callback=?", req, function(data) {

//create array for response objects
var suggestions = [];

//process response
$.each(data, function(i, val){
suggestions.push(val.cb_activitiesterm);
});

//pass array to callback
add(suggestions);
});
},

//define select handler
select: function(event, ui) {

//create formatted activity
var activity = ui.item.value,
span = $("<span>").text(activity),
a = $("<a>").addClass("remove").attr({
href: "javascript:",
title: "Remove " + activity
}).text("x").appendTo(span);

//add interest to activities div          
                        span.insertBefore("#cb_activitiesterm");
                               
                  //add activity to hidden form field
             $('input[name=cb_activities]').val(activity);
},

//define select handler
change: function() {

//prevent 'to' field being updated and correct position
$("#cb_activitiesterm").val("").css("top", 2);
}
});

//add click handler to activities div
$("#activities").click(function(){

//focus 'to' field
$("#cb_activitiesterm").focus();
});

//add live handler for clicks on remove links
$(".remove", document.getElementById("activity")).live("click", function(){

//remove current activity
$(this).parent().remove();

//add activity to hidden form field
             $('[name=cb_activities]').val().remove();

//correct 'to' field position
if($("#activities span").length === 0) {
$("#cb_activitiesterm").css("top", 0);
}
});
});
</script>

And then my 3rd issue is if you remove a value it needs to also remove it from the hidden input. I've tried several attempts of the remove() in the onclick function but I can't get it to remove it.

Lol other than that it's cherry.
Any advice on any of these would be great.

I don't even know what I'm trying to do.. but I can't figure out how to do it.. or how I should do it.

I have a table with a bunch of rows in it.. let's say 10 rows. I want to output two fields from each one of those rows wherever I want on my page. So if I do it this way, I can't use:

Code: [Select]
while($result = mysql_fetch_array($query)){
I can't use that because that assumes I want to repeat each line. However, I don't want to use a new query each time because the actual number I'll be using is closer to 100. So if that makes sense.. does anyone have any ideas?

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=318312.0

Hi all,

I have the following MySQL insert query:
Code: [Select]
$insert= mysql_query   ("INSERT INTO tablename (column1,`".$EXPfields."`) VALUES ('$something','".$EXPvalues."')");
where $EXPfields is an array of table-field-names and $EXPvalues is an array of table-field-values.

Now I want to write an equivalent query, but using UPDATE instead of INSERT INTO, but I don't want to write out all the field names/values separately, but again want to use $EXPfields and $EXPvalues.

So something like this:
Code: [Select]
$update = mysql_query ("UPDATE tablename
                     SET (column1,`".$EXPfields."`) = ('$something','".$EXPvalues."')
                     WHERE ....
                  ");

Is this possible? If so, what is the proper syntax? Thanks!

Hey,
Im needing some help with an update form that I have created using PHP/MySQL. Basically the form is working great (updating records, retrieving records and showing errors) but there is a problem with the errors.

If there are no errors and I edit the existing text input values, the database updates fine. However if I edit the existing text input values and there is an error, the edited input values dont save, they revert back to the values of the database.

I understand why this is happening, its because the text inputs are set to show the values of the database so if i edit the existing values and an error occurs or the page is refreshed, the values revert back to the database values. However id like it so that if any text in the text field have been edited and an error occurs, the text stays to how it is until the user by passes any errors and then the database is updated.

Hopefully this makes sense. I have attached the php page just incase anybody wants to look into it but any help would be much appreciated!

Also, im not looking for someone to just to fix this for me but if someone knows my problem and can guide me in the right direction, that would be great.

Hello All,   Being a newbie at PHP coding I'm at my wits end trying to figure out:      a) how to pull-in values from a delimeted text field (in a MySQL table) and check/select the appropriate checkboxes, based on the values that were stored in the text field,      b) how to write any changes (made by the user) back to the tables' text field.   Note that this is for an "update.php" file/process.   My "create.php" file/process uses the following HTML to display and accept the checkbox values:

                <div class="control-group">
                    <label class="control-label">Limited Time Sale Days:</label>
                    <div class="lts-checkboxes-container">
                        <label class="indent-to-the-left">
                               <input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='F'><span class='no-highlight'>Fri</span>
                        </label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Sa'><span class='no-highlight'>Sat</span></label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Su'><span class='no-highlight'>Sun</span></label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='M'><span class='no-highlight'>Mon</span></label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Tu'><span class='no-highlight'>Tue</span></label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='W'><span class='no-highlight'>Wed</span></label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Th'><span class='no-highlight'>Thu</span></label>
                    </div>
                </div>

And the code (certainly not the best code in the world, but it works) to collect, delemit/concatenate, and save to DB is as follows:

        if(empty($_POST['limited_time_sale']))
            {
                // echo("You didn't select any weekday.");
                $selected_lts = '';
                $limited_time_sale = '';
            }
        else
            {
                $limited_time_sale = $_POST['limited_time_sale'];    
                $N = count($limited_time_sale);
                $selected_lts = '';
                 // echo("You selected $N DoW(s): ");
                for($i=0; $i < $N; $i++)
                    {
                        // echo($limited_time_sale[$i] . " ");
                        if ($i < ($N - 1))
                            {
                                $selected_lts .= $limited_time_sale[$i] . "-";
                            }
                        else
                            {
                                $selected_lts .= $limited_time_sale[$i];
                            }
                    }
            }
 
        if(!empty($selected_lts)) { $limited_time_sale = $selected_lts; }

Now, I've figured out how to bring-in, and separate the stored values using the following code, however I have no idea what to do next...in order to have only the approprite boxes checked/selected (in the event that all boxes were not selected during the create stage).

                $limited_time_sale = isset($values['limited_time_sale']) ? $values['limited_time_sale'] : '';                
                $checked_lts = explode("-", $limited_time_sale);

In my "update.php" file, the HTML for the forms' checkboxes is as follows:  

                <div class="control-group">
                    <label class="control-label">Limited Time Sale Days:</label>
                    <div class="lts-checkboxes-container">
                        <label class="indent-to-the-left">
                               <input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='F' <?php echo $lts1; ?>><span class='no-highlight'>Fri</span>
                        </label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Sa' <?php echo $lts2; ?>><span class='no-highlight'>Sat</span></label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Su' <?php echo $lts3; ?>><span class='no-highlight'>Sun</span></label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='M' <?php echo $lts4; ?>><span class='no-highlight'>Mon</span></label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Tu' <?php echo $lts5; ?>><span class='no-highlight'>Tue</span></label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='W' <?php echo $lts6; ?>><span class='no-highlight'>Wed</span></label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Th' <?php echo $lts7; ?>><span class='no-highlight'>Thu</span></label>
                    </div>
                </div>

    Attached picture shows that selected checkboxes are saved to the text field as/in the format of "F-Sa-Su-M-Tu-W-Th" - if all checkboxes/weekdays were selected....or as "Sa-M-W-Th" - if only Sat, Mon, Wed, and Thu checkboxes were selected   Thanks.   Attached Files  checkbox-Question.png   403.99KB   0 downloads