PHP - Sort List From A Join

Hi , I have one question .. Can I split showing of content of dynamic list in 2 parts , when I echo list in code ..

Code: [Select]
<?php 
// Run a select query to get my letest 8 items
// Connect to the MySQL database  
include "../connect_to_mysql.php"; 
$dynamicList = "";
$sql = mysql_query("SELECT * FROM products ORDER BY date_added DESC LIMIT 8");
$productCount = mysql_num_rows($sql); // count the output amount
if ($productCount > 0) {
while($row = mysql_fetch_array($sql)){ 
             $id = $row["id"];
 $product_name = $row["product_name"];
 $price = $row["price"];
 $date_added = strftime("%b %d, %Y", strtotime($row["date_added"]));
 $dynamicList .= '<table width="100%" border="2" cellspacing="2" cellpadding="2">
        <tr>
          <td width="17%" valign="top"><a href="product.php?id=' . $id . '"><img style="border:#666 1px solid;" src="inventory_images/' . $id . '.jpg" alt="' . $product_name . '" width="77" height="102" border="2" /></a></td>
          <td width="83%" valign="top">' . $product_name . '<br />
            $' . $price . '<br />
            <a href="product.php?id=' . $id . '">View Product Details</a></td>
        </tr>
      </table>';
    }
} else {
$dynamicList = "We have no products listed in our store yet";
}
mysql_close();
?>


Code: [Select]
<p><?php echo $dynamicList; ?><br />
</p>
It works ok, and putting my files, everything works, but when I put 8 pictures with price and other details, it just show one image with details and another image below with details, and the third image below and so on ..
Can I split dynamic list to show 4 images with details on the left side and 4 on the right side?

Thank you in advance for help , if is possible

Im currently working on a new feature called 'Request Player'. Basically what this does is allows managers to request players within their club for a upcoming fixture. There a multiple teams within a club (Senior As, Senior Bs, Colts U21s). Each team is controlled by a manager. And then they can add players as they need.

Anyway, I'm having problems with the sql JOIN.

I have currently got this:

$check = "SELECT u.* FROM users AS u LEFT JOIN requests AS r ON (u.id = r.player_id) WHERE (u.id = r.player_id)";


I have got two tables.

users - all the managers and players.
requests - all the requests the managers make to other teams within their club.

In the request table i have got this:

id
player_id - the players that has been requested.
fixture_id - the fixture the manager wants to have that player for.
accepted - if he has been accepted or not. This comes later on.

At the moment i have got it working so that it inserts this data, although the code is working but i want to have a mysql query that checks if a manager has any incoming requests from other teams within the club. BUT only request that has been made to his team....

Could someone please help me out?

I'm trying to figure out the best way to do this if I'm doing it right with my for loop. With how many numAnswers there are for the selected poll its going to put the div with the label and text box for each of those but its giong to go and tie in each of those pollAnswers with what ID of the pollAnswer to the pollAnswer table.


Code: [Select]
$pollsQuery = "
    SELECT
        polls.question,
        polls.statusID,
        polls.numAnswers,
        DATE_FORMAT(polls.dateExpires, '%m/%d/%Y') AS dateExpires
    FROM
        polls
    WHERE
        polls.ID = '" . $pollID . "'";
$pollsResult = mysqli_query ( $dbc, $pollsQuery ); // Run The Query
$row = mysqli_fetch_array ( $pollsResult, MYSQL_ASSOC );

$pollAnswers = "
    SELECT
        pollAnswers.ID,
        pollAnswers.answer
    FROM
        pollAnswers
    WHERE
        pollAnswers.pollID = '" . $pollID . "'";
$pollAnswersResult = mysqli_query ( $dbc, $pollAnswers ); // Run The Query

Code: [Select]
<fieldset class="answerLeg">
        <legend>Edit Poll Answers</legend>
        <?php for ( $j = 0; $j <= $numAnswers; $j++) { ?>
            <div class="field required answers">
                <label for="answer<?php $j?>">Answer <?php $j?></label><input type="text" class="text" name="answer<?php $j?>" id="answer<?php $j?>" title="Answer <?php $j?>"/>
                <span class="required-icon tooltip" title="Required field - This field is required, it cannot be blank, and must contain something that is different from emptyness in order to be filled in. ">Required</span>     
            </div>
        <?php } ?>
    </fieldset>

First off Please bear with me. I am newbie.

Here is a table name 'Employes' and has following Columns.
ID(PK) | FirstName | LastName | SecurityLicence | CrowdLicence | DriversLicence | Password |

And through form all the values are assigned to these columns and user gets registered.
I have done it using this code and its working fine.
Code: [Select]
<?php
session_name('YourVisitID');
session_start();
if(!isset($_SESSION['FirstName']))
        {
                header("Location: http://" . $_SERVER['HTTP_HOST'] . dirname($_SERVER['PHP_SELF']) . "/index.php");
                exit();
        }
else
{
$page_title = 'Register';
include('templates/header.inc');
if(isset($_POST['submit']))
{
        require_once('mysql_connect.php');
        // connect to the db
        
        //Create a function for escaping the data.
        function escape_data($data)
        {
                global $con; // need connection
                if(ini_get('magic_quotes_gpc'))
                {
                        $data = stripslashes($data);
                }
                return mysql_real_escape_string($data, $con);
        }
        $message = NULL;
        if(empty($_POST['firstName']))
        {
                $fn = FALSE;
                $message .= '<p>you forgot to enter your first name!</p>';
        }
        else
        {
                $fn = escape_data($_POST['firstName']);
        }
        
        if(empty($_POST['lastName']))
        {
                $ln = FALSE;
                $message .= '<p>You forgot to enter your last name!</p>';
        }
        else
        {
                $ln = escape_data($_POST['lastName']);
        }
        if(empty($_POST['licenceId']))
        {
                $li = FALSE;
                $message .='<p>You Forgot enter your Security Officer Licence Number!</p>';
        }
        else
        {
                $li = escape_data($_POST['licenceId']);
        }
        if(empty($_POST['crowdLicenceNo']))
        {
                $cln = FALSE;
                $message .='<p>You Forgot to enter your Crowd Controller Licence Number!</p>';
        }
        else
        {
                $cln = escape_data($_POST['crowdLicenceNo']);
        }
        if(empty($_POST['driverLicenceNo']))
        {
                $dln = FALSE;
                $message .='<p>You forgot to enter your Driving Licence Number!</p>';
        }
        else
        {
                $dln = escape_data($_POST['driverLicenceNo']);
        }
        if(empty($_POST['password']))
        {
                $p = FALSE;
                $message .='<p>You forgot to enter your password!</p>';
        }
        else
        {
                        if($_POST['password'] == $_POST['password2'])
                                {
                                        $p = escape_data($_POST['password']);
                                }
                        else
                                {
                                        $p = FALSE;
                                        $message .='<p>Your password did not match the confirmed password</p>';
                                }
        }
        if($fn && $ln && $li && $cln && $dln && $p)
        {
                                
                $query = "SELECT ID FROM Employes WHERE SecurityLicence='$li'";
                $result = @mysql_query($query);
                if(mysql_num_rows($result) == 0)
                { 
                
                
                $query = "INSERT INTO Employes (FirstName, LastName, SecurityLicence, CrowdLicence, Driverslicence, Password)
                VALUES ('$fn', '$ln', '$li', '$cln', '$dln', PASSWORD('$p'))";
                $result = @mysql_query($query);
                        if($result)
                                {
                                        echo '<p>You have been registered</p>';
                
                                }
                        else
                        {
                                $message = '<p>We apologise there is a system error.</p><p>' . mysql_error(). '</p>';
                        }
                } 
                else 
                {
                        $message = '<p>That Security Licence is already registered</p>';
                }
                        mysql_close();
        }
        else
        {
                $message .='<p>Please try again</p>';
        }
}

//print the message if there is one
if (isset($message))
{
        echo '<font color="red">', $message, '</font>';
}
}
?>
<script type="text/javascript">
function validate_form()
{
        var f = document.forms["regForm"]["firstName"].value;
                if(f==null || f=="")
                        {
                                alert("First Name must be filled out");
                                return false;
                        }
        var l = document.forms["regForm"]["lastName"].value;
                if(l==null || l=="")
                        {
                                alert("Last Name must be filled out");
                                return false;
                        }
        var sl = document.forms["regForm"]["licenceId"].value;
        var s = /^\d{5,}$/g.test(sl);
        var sll = sl.length;
                if(s==false)
                        {
                                alert("Security Licence No must be filled out in digits");
                                return false;
                        }
                else if(sll>=7)
                        {
                                alert("Invalid Security Licence No");
                                return false;
                        }
        var csl = document.forms["regForm"]["crowdLicenceNo"].value;
        var k = /^\d{5,}$/g.test(csl);
        var csll = csl.length;
                if(k==false)
                        {
                                alert("Crowd Controller Licence No must be filled out in digits");
                                return false;
                        }
                else if(csll>=7)
                        {
                                alert("Invalid Crowd Controller Licence No");
                                return false;
                        }
        var d = document.forms["regForm"]["driverLicenceNo"].value;
        var v = /^\d{6,}$/g.test(d);
        var dl = d.length;
                if(v==false)
                        {
                                alert("Driver's Licence No must be filled out in digits");
                                return false;
                        }
                else if(dl>=11)
                        {
                                alert("Invalid Driver's Licence No");
                                return false;
                        }
       
}
</script>


<h3>Employment Registration Form</h3>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" name="regForm" method="post" onsubmit="return validate_form()">
<fieldset>
<legend>Enter informations in the form below</legend>
<table>
<tr><th>First Name:</th><td><input type="text" name="firstName" /></td></tr>
<tr><th>Last Name:</th><td><input type="text" name="lastName" /></td></tr>
<tr><th>SO Licence No:</th><td><input type="text" name="licenceId" size="5" /></td></tr>
<tr><th>CC Licence No:</th><td><input type="text" name="crowdLicenceNo" size="5" /></td></tr>
<tr><th>Driver's Licence No:</th><td><input type="text" name="driverLicenceNo" size="10" /></td></tr>
<tr><th>Create Password:</th><td><input type="password" name="password" size="10" /></td></tr>
<tr><th>Confirm Password:</th><td><input type="password" name="password2" size="10" /></td></tr>
</table>
<input type="submit" name="submit" value="Register" />
<input type="reset" value="Reset" />
</fieldset>   
</form>

<?php
include('templates/footer.inc'); 
?>
 

Here is another Table "Jobs", with the following columns.
JobID(PK) | ID(FK) | JobDate | JobStart | JobFinish | JobLocation | RequestedBy |

For Example I am the owner of the company and want to assign jobs to my registerd workers using a form.
I assign JobDate=12/12/12 JobStart=1900 JobFinish=2300 JobLocation=Perth Requestedby=John. And I do it using form. JobID will be incremented automatically.
Now what i dont understand is how do I assign ID(FK) to it?? I am newbie this question may sound stupid to many of you but hey please help.
Or should I replace ID(FK) with the SecurityLicence of the worker, Like this
JobID(PK) |ToSecurityLicence | JobDate | JobStart | JobFinish | JobLocation | RequestedBy |

What I want is when the workers signs in He should see the job assigned to him. Can I do it using inner join??? if so how to right PHP code for comparing these two tables???
I am way too much confused.

Too many of you I have made a fool of myself asking this question. But I believe a person who doesnt ask the question is fool foreva.

If somebody could Help I would really really aprreciate it.

Thanks.

Hello everyone,   I am hopping someone can help me sort out a query.   I have two tables, one table is a players table, and a second table is a player_vitals data that stores heights and weight changes.  I want to list all the players showing the latest height and weight change.   this query will not fetch the latest height and weight   SELECT a.player_id, a.player_first_name,a.player_last_name, b.player_height, b.player_weight

Hi

I want to know I can select which ID I get from an inner join?

Not sure If I should post here or in the MYSQL forum, but as it's using OOP I posted here.


Code: [Select]
$sql = "SELECT * FROM area_county INNER JOIN area_country ";
$sql .= "ON area_county.country_id = area_country.id ";
$sql .= "ORDER BY area_country.country, area_county.county ASC ";
$sql .= "LIMIT {$per_page} ";
$sql .= "OFFSET {$pagination->offset()}";
$list = Area_county::find_by_sql($sql);

foreach($list as $lists){
$ID = $lists->id;
$country_id = $lists->country_id;
$county = ucwords($lists->county);
        $country = ucwords($lists->country);

echo $ID;
}


I want it to show the ID of the county, NOT the id of the country.
both tables in the database have the ID column called id.

I know this can be done, but not to sure how.


Thanks

Not sure if it is because it is too early in the morning but I am having a problem.

Code: [Select]
$mpid = #

$query = "SELECT table1.title, table2.* ".
 "FROM table1, table2 ".
"WHERE table1.mpid = table2.mpid";

How do I only show the info from the 2 tables where mpid=#

I'm trying to pull from my database every spell or attack that is equal to `All`, but it doesn't seem to be working. All it is doing is pulling all the spells, regardless of class = `All`, and also 0 of the attacks. It should be producing a single attack that is = to `All` as no spells are = `All`.

Any help would be greatly appreciated! Thanks! (This is my first attempt at a JOIN statement...)


$query = "SELECT attacks.id, attacks.name, attacks.price, attacks.class, attacks.descript, spells.id, spells.name, spells.price, spells.class, spells.descript ".
"FROM attacks, spells ".
"WHERE attacks.class = 'All' || spells.class = 'All' order by attacks.name, spells.name asc";

I'm an amateur PHP/mySQL person, trying to learn some more things, and I've hit a snag trying to do something I haven't done before.

Okay, here's what I'm trying to do. I have 3 tables:

Items (which includes `id` as the primary key) and a TON of information about each item.
Shop (which includes `id` as the primary key) and shop name and location.
Sale which has shop_id and item_id.

So, what I'm trying to do is have a form where someone can input in an item name and a shop name and have that item then added to the shop. This is where the Sale table comes into play too, as that's going to house each of the items and what shop it's in.

I currently have the following queries:


       $query5 = $db->execute("SELECT id FROM items LEFT JOIN sale ON items.id=sale.item_id");
       $query6 = $db->execute("SELECT id FROM shop LEFT JOIN sale ON shop.id=sale.shop_id");


I have the following form:


        if ($name == "") { $errors++; $errorlist .= "Name is required.<br />"; }
        if ($location == "" ) { $errors++; $errorlist .= "Location is required.<br />"; }
if (!isitem($itemname)) die("That item does not exist."); }

        if ($errors == 0) { 
            $query = doquery("INSERT INTO `sale` SET `item_id`=`$item`");
            admindisplay("New Item Added.","Add Items");

$page = <<<END

<b><u>Add Item</u></b><br /><br />
<form action="create_shop.php?do=additem" method="post">
<table width="90%">
<tr><td width="20%">Name:</td><td><input type="text" name="itemname" size="30" maxlength="255" value="" />**Be sure the item is already in the database and matches it IDENTICALLY.</td></tr>
<tr><td width="20%">Location:</td><td><input type="text" name="location" size="30" maxlength="55" value="" /><br /><span class="small">Where is the item obtained (unique shop name, please!)</span></td></tr>
</table>
<input type="submit" name="submit" value="Submit" /> <input type="reset" name="reset" value="Reset" />
</form>


What next steps do I need to take? Am I even on the right path?

Thanks for any and all help anyone provides!! I really appreciate it!

Hi, I use the following code to create a select menu from an array of options stored in LISTS.php:


include 'LISTS.php';

print('<select id="from" name="from">');
foreach ($langList as $lang) {printf('<option %s>%s</option>', 
     ($from1 == $lang ? 'selected="selected"' : ''), $lang);
     }
echo '</select>';


where LISTS.php includes the following:
$langList = array(' ','English', 'French', 'German', 'Dutch', 'Spanish');

This works great, but now I want to do something similar with a checkbox list, where each checkbox has an associated 'onchange' javascript function and I'm getting pretty stuck.

My checkbox list is of the following form:

Code: [Select]
<html>
<ul style="height: 95px; overflow: auto; width: 200px; border: 1px solid #480091; list-style-type: none; margin: 0; padding: 0;">
    <li id="li1b"><label for="chk1b"><input name="chk1b" id="chk1b" type="checkbox" onchange="function1('chk1b','li1b')">Option1</label></li>
    <li id="li2b"><label for="chk2b"><input name="chk2b" id="chk2b" type="checkbox" onchange="function1('chk2b','li2b')">Option2</label></li>
//etc.
 </ul>
</html>
What I want to do is have 'Option1', 'Option2', etc. stored in an array in LISTS.php and have a PHP script that populates the checkbox list accordingly, in a similar manner to my select menu above. I can't work out how to get the ID of the next <li> and the next <input> in the list to go up by one each time, e.g. 'li1b' then 'li2b', 'li3b', etc.

Could someone pls help me out? Thanks!

Hi,

Been looking for a little while on this problem, but can't seem to find a solution.

Ok, so I have two tables,

gallery & users

Gallery contains images, image info etc.
Users contains usernames, emails etc.

In users there is a 'photo' column, which references to an ID in the gallery table. This is so each user can have a 'profile' image from a bank of images stored in gallery.

What i'm trying to do is have a query that will output all of the images, and then show the usernames of assigned to each photo if there is one.

Heres what i've got:

Code: [Select]
$result = mysql_query("SELECT *,COUNT(photo) AS usercount FROM gallery LEFT JOIN users ON gallery.img_id=users.photo GROUP BY img_id ORDER BY date_uploaded ASC");
Its all fine, and displays the username, though if there are multiple users using the same photo, it only shows one username.
However, when displaying 'usercount', it will show that there are 2+ or whatever.

Short of nesting queries within other loops, is there an alternative solution??

Thanks!
Edd

I need to get the friends password and insert it into the table so the friend can delete it later can some one help me do a table join
<?php
//include the connect script
include "../../../../connect.php";

//Post variables from flash
$username = $_POST['username'];
$password = $_POST['password'];
$status = $_POST['status'];
$friend = $_POST['friend'];

$username = stripslashes($username);
$password = stripslashes($password);
$status = stripslashes($status);
$friend = stripslashes($friend);
$username = mysql_real_escape_string($username);
$password = mysql_real_escape_string($password);
$status = mysql_real_escape_string($status);
$friend = mysql_real_escape_string($friend);

$sql = mysql_query("SELECT * FROM user_declined_list  WHERE username = '$username' and password = '$password' and friend = '$friend' and status ='$status'");
$rows = mysql_num_rows($sql);
$your_username=$rows['username'];



if($rows > 0)
{
echo "&msgTextFriendShipRejected= RESEND NEW FRIEND REQUEST SUCCESSFULLY!";

// ok they are now our friend but we are not thier friend so lets insert the friendship in reverse and set the status to 1 on both ends
// Problem i see is this if the user wishs to remove the friend later he will neen his password set to delete from this table.
// so i need to do a join table again where friend password gets inserted here..
$insertnewfriend = mysql_query("INSERT INTO user_friends_list (username,password,friend,status) VALUES ('$friend','$password','$username','0')") or die(mysql_error());
//ok lets update the request and set the friend to be a friend becuase we said yes be my friend.
$deleteuserfriend = mysql_query("DELETE FROM user_declined_list  WHERE username = '$username' and password = '$password' and friend = '$friend' and status ='0'");
return;
}


?>

I am using the following sql code to display a stream of statuses, I have the username/profile picture/status/likes working but I am now stuck on comments.

SELECT s.*, u.*, c.*, COUNT(l.likes_location_id) AS likeCount
FROM stream AS s
LEFT JOIN users as u ON (u.users_username = s.stream_username)
LEFT JOIN comments AS c ON ( c.comments_location_id = s.stream_id )
LEFT JOIN likes AS l ON ( l.likes_location_id = s.stream_id )
 GROUP BY s.stream_id
ORDER BY s.stream_id DESC LIMIT 50

If a status has multiple comments the results only shows the first comment that was made. How can I get it to include all of them?   Also, how would I include this in the PHP loop, without having another loop?

Thanks,
Edited by slj90, 06 January 2015 - 08:56 PM.

My query is 38 lines long so here is the simple version. Note: frequency is a number input by user from 0-720, 0 is the feature off.

$result = mysql_query("SELECT * FROM main_table
LEFT OUTER JOIN secondary_table ON main_table.cid=secondary_table.cid
WHERE
((frequency = 0) OR (fc_timestamp IS NULL) OR ('$current_timestamp'-frequency*3600 > fc_timestamp))
");

The data looks like this: (main_table cid is a unique id and in secondary there is a auto increment id column)

main_table
---------------
| cid* | a | b |
| 1  |  0 | 0 |
| 2  |  1 | 0 |
| 3  |  1 | 1 |
| 4  |  0 | 0 |
---------------

secondary_table
---------------
| cid | user_id | fc_timestamp |
| 1  |    5     |  1420417976  |
| 1  |    7     |  1420417999 |
| 2  |    9     |  1420417977 |
| 2  |    9     |  1420418976 |
| 2  |    111   |  1420419976 |
| 2  |    134   |  1420427976 |
| 3  |    111   |  1420417986 |
| 4  |    1001  |  1420417876 |
-------------------------------

What is happening is the query is pulling each line from the secondary table so I get multiple cid's as the result. I only want the row in the joined secondary table that is the maximum value of fc_timestamp.

Hello, I'm trying to get a join with 2 tables but cannot get it to work. It's a contentsystem with an ID for the author. Another table tells the name of the author. Now I want to join these things. This is what i've coded so far:

<?
$query = "SELECT rmnl_content.content_aid, COUNT(rmnl_content.content_id), rmnl_crew.crew_id, rmnl_crew.crew_name

FROM rmnl_content, rmnl_crew" "where rmnl_content.content_aid = rmnl_crew.crew_id";
   
$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
   echo " <b>". $row['COUNT(rmnl_content.content_id)'] ."</b> posted messages by ". $row

['crew_rmnl.crew_name'] ."</td> .";
   echo "<br />";
}
?>