PHP - Single Result Sql Code

Quote from: Hollows on August 20, 2010, 11:29:25 AM

I have an array that is pulling 10 results from a db like this -


<?php
$result = mysql_query("SELECT * FROM inbox ORDER BY id DESC LIMIT 0,10");
while ($row = mysql_fetch_array($result)) {

<div class="<?php echo $row['class'];  ?>">
<?php
echo $row['name'];
echo $row['message'];
?>
</div>


basically 'class' is defined in an earlyier part of the script and is used to tell the css what class to display - as I'm sure you can see.

What I'd like todo then be able to update the class of a message once I have read it, so update the class table with something like 'read' so the css will then be based on .read not what ever was set by the form, either using a link or form button inside each of the 10 results from the array.

I've managed to update one by using -
Code: [Select]
mysql_query("UPDATE shoutbox SET class = 'read' WHERE id = ''XX"); // XX should be row id

But I had to set that myself and point the id to the row id I wanted to change - How can I have that already populated with the row for the id that triggered it?


To be clear so it looks like -

Name
Message
read button
_____________
Name
Message
read button
_____________
Name
Message
read button
_____________

and so on...


Would I be better going by check boxes and one submit button? If so how could I implement that?


I can provide the full code I've written if it helps

Thank you in advance

Hi all,

Just to check if it is possible to have 2 while loops in a single code? Below are my codes.


SQL code

$tutor_id = mysqli_real_escape_string($dbc, $_GET['tutor_id']);
    $query = "SELECT tl.level_id, tl.level_name,
                     ts.subject_id, ts.subject_name, tsl.subject_level_id, 
                     IF(tosl.tutor_id='{$tutor_id}', 1, 0) as checked
              FROM tutor_level AS tl
              INNER JOIN tutor_subject_level AS tsl
                  USING (level_id)
              INNER JOIN tutor_subject AS ts
                  USING (subject_id)
              LEFT JOIN tutor_overall_level_subject AS tosl
                  ON tosl.subject_level_id = tsl.subject_level_id
                  AND tosl.tutor_id = '{$tutor_id}'
              ORDER BY tl.level_id, ts.subject_name";
    $sql = mysqli_query($dbc, $query) or die(mysqli_error($dbc));

$query1 = "SELECT tl.level_id, tl.level_name,
                     IF(tslvl.tutor_id='{$tutor_id}', 1, 0) as checked
              FROM tutor_level AS tl
              LEFT JOIN tutor_selected_level AS tslvl
                  ON tslvl.level_id = tl.level_id
                  AND tslvl.tutor_id='{$tutor_id}'
              ORDER BY tl.level_id, tl.level_name";
    $sql1 = mysqli_query($dbc, $query1) or die(mysqli_error($dbc));

PHP code (2 while loops)

//Process the results
    $checkboxes = ''; //Create variable for output
    $current_level_id = false; //Flag to check when records change level
   while($data = mysqli_fetch_array($sql)) //Iterate throug the DB results
    {
        if($current_level_id != $data1['level_id']) //Determine if this level is different from the last
        {

print_r ($data);
            $checkboxes .= createLevelCheckboxes($subject_data, $level_data, 5);
            $current_level_id = $data['level_id'];
            $subject_data = array();
        }
        //Add the current record to the $level_data array
        $subject_data[] = $data;
    }

//$checkboxes .= createLevelCheckboxes($subject_data, $level_data, 5);

while($data1 = mysqli_fetch_array($sql1))

{

print_r ($data1);

$checkboxes .= createLevelCheckboxes($subject_data, $level_data, 5);
            $level_data = array();

$level_data[] = $data1;

}

    //Call the createLevelCheckboxes() function to generate the HTML for the LAST level records
    $checkboxes .= createLevelCheckboxes($subject_data, $level_data, 5);

hello.

I have some oop code that pulls information about a page out of a mysql db but i dont want to put the code on every page. Instead in want to put it in the a function and pass it back to each page.

do i put it in a function in my functions.php file or can i put it into my includes/pages.php includes file ??

the code looks for the page name on the page and then pulls that pages info from the db.
this code works on the page - home.php

Code: [Select]
<?PHP
require_once("../includes/initialize.php");

$currentPages = Pages::find_all();

$pName = "adminHome";

$page_id = "";
$visible = "";
$pageZones = "";
$pageCRUD = "";
$pageTypes = "";
$module = "";
$title = "";
$sub_title = "";
$description = "";
$image = "";
$about = "";


foreach($currentPages as $currentPage){

$page_id = $currentPage->id;
$pageName = $currentPage->pageName;
$visible = $currentPage->visible;
$pageZone = $currentPage->pageZones_id;
$pageCRUD = $currentPage->pageCRUD_id;
$pageType = $currentPage->pageTypes_id;
$module = $currentPage->module_id;
$title = $currentPage->title;
$sub_title = $currentPage->sub_title;
$description = $currentPage->description;
$image = $currentPage->image;
$about = $currentPage->about;



if($pageName == $pName){

echo $pageName.'<br/>';
echo $page_id.'<br/>';
echo $visible.'<br/>';
echo $pageZone.'<br/>';
echo $pageCRUD.'<br/>';
echo $pageType.'<br/>';
echo $module.'<br/>';
echo $title.'<br/>';
echo $sub_title.'<br/>';
echo $description.'<br/>';
echo $image.'<br/>';
echo $about.'<br/>';
}
}
?>

i would like to be able to just put global $page or something like that to get all the db info for each page.


i tried this in my  includes/pages.php includes file but it cant get the page name.

Code: [Select]
public static function find_by_pageName(){
global $database;
global $pName;

$sql = "SELECT * FROM ".self::$table_name." WHERE pageName=".$pName."";
$result_array = self::find_by_sql($sql);
return !empty($result_array) ? array_shift($result_array) : false;
}



any suggestions ???? whats the best way to do this ?

thanks
ricky

Hey Everyone,

I am posting today because I have a site I am working on which uses the xzero classifieds and having a small problem accomplishing something with it. I am trying to relocate the ad count number (of the city the user is currently browsing) to a box in my sidebar which has various site statistics like the following

Online Users: XX
Ads in this city: 0

But my code keeps giving me a 0 (even after I posted some test ads)

Anywho, heres what I have so far but cant seem to make it work

<?php
$country_adcounts = array();
$city_adcounts = array();
$sql = "SELECT ct.cityid, c.countryid, COUNT(*) as adcnt
FROM $t_ads a
INNER JOIN $t_cities ct ON ct.cityid = a.cityid AND ($visibility_condn)
INNER JOIN $t_countries c ON ct.countryid = c.countryid
WHERE ct.enabled = '1' AND c.enabled = '1'
GROUP BY ct.cityid";

$res = mysql_query($sql) or die(mysql_error().$sql);

while($row=mysql_fetch_array($res))
{
$country_adcounts[$row['countryid']] += $row['adcnt'];
$city_adcounts[$row['cityid']] += $row['adcnt'];
}

$adcount = 0+$city_adcounts[$city['cityid']];
?>

and this is the code which should recall the adcount

<?php echo $adcount; ?>

What am I doing wrong? I'm placing the first code on my sidebar and attempting to call the ad count through an echo. So ya, if anyone could tell me why this isn't working or lend a hand in helping me fix it please know I'll greatly appreciate it. 

Thanks.

I have a database that contains results of fantasy football games.  I have a query that returns the results of the games team X played against the other teams in the league.  The query works perfect in the database.

Results are
Quote

Opponent Name:    America Enforcers
Wins    Losses    Draws
1                1                0
Last Game:    Week 2 of 2011: 416.6 - 369.8



the query and the php code cycle thru each team that team X has played and brings up team X's record and the results of the last game played.

My problem is that the code stops one team short.  So Team X played teams A, B, C, D, E
but the code only returns teams A, B, C, D
E is left off. 

I can't for the life of me figure out what silly mistake I made.

Code: [Select]
<?php 
include_once('../other/functions.php');
$con = mysql_connect($hostname, $username, $password) 
OR DIE ('Unable to connect to database! Please try again later.');
$db = mysql_select_db($dbname, $con);
$thing = $_GET['thing'];
$query = "select selected.teamname AS selected_team, selected_score.score AS selected_score, week.week, year, home_id, ". 
"target_score.score as target_score, target.teamname as targetname, week.ID ".
"from owners as selected ".
"JOIN game_scores AS selected_score ON selected.owner_id = selected_score.team_id ".
"JOIN game_setup ON game_setup.game_id = selected_score.game_id ".
"JOIN game_scores AS target_score ON target_score.game_id = game_setup.game_id AND target_score.team_id != selected_score.team_id ".
"JOIN owners AS target ON target.owner_id = target_score.team_id ".
"JOIN week ON week.week = game_setup.week ".
"WHERE selected.owner_id = $thing ".
"and target.active = 1 ".
"GROUP BY target.teamname, year, week.ID ";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
$result1 = mysql_query($query);
$row1 = mysql_fetch_array($result1);
if (!$result) {
    die('Invalid query: ' . mysql_error());
}
$wins=0;
$losses=0;
$draws=0;
$last_target = false;
echo '<h2>' . $row1['selected_team'] . ' vs. Active Teams</h2>';
while ($row = mysql_fetch_assoc($result))
{
    if ($last_target['targetname'] != $row['targetname'])
    {
        if ($last_target)
        {
            printf('
<table border="1" width="600">
<tr>
<th>Opponent Name:</th>
<th colspan="2">%s</th>
</tr>
<tr>
<th>Wins</th>
<th>Losses</th>
<th>Draws</th>
</tr>
<tr>
<td align="center">%d</td>
<td align="center">%d</td>
<td align="center">%d</td>
</tr>
<tr>
<th>Last Game:</th>
<td colspan="2">%s of %s: %.1f - %.1f</td>
</tr>
</table><br/>',
$last_target['targetname'],
                $wins,
                $losses,
                $draws,
                $last_target['week'],
                $last_target['year'],
                $last_target['selected_score'],
                $last_target['target_score']
);    
        }
        $wins = $losses = $draws = 0;
    }
    if ($row['selected_score'] < $row['target_score'])
        ++$losses;
    elseif ($row['selected_score'] == $row['target_score'])
        ++$draws;
    else
        ++$wins;
    
    $last_target = $row;
}

?>



Any ideas on how to make the magic elvish be good???
Thank you for your time.