PHP - Only 3 Queries Are Done, But Not Any More

Hello, in short, here is my problem:

There are 2 tables:

a table called postcodes, which contains UK postcodes
and a table called wp_bp_xprofile_data (i want to integrate this search with the Buddypress plugin's tables) which contains the user data.

I tried searching this, but to be honest, i didn't quite know how to go about it..



Here is what i have:

$query1 = "SELECT value, user_id, (SQRT(POW((b.x - a.x), 2) + POW((b.y - a.y), 2))/1000) * 0.621 AS distance
    FROM postcodes a, postcodes b, wp_bp_xprofile_data
    WHERE a.outcode = '"$postcode"' AND b.outcode = wp_bp_xprofile_data.value
    HAVING (distance < '"$area"')
    ORDER BY distance asc ";


$result1=mysql_query($query1) or die(mysql_error());

        echo "<p style=\"font-size:10px;line-height:14px;color:#888;\">Straight line distances shown.<br>";
        echo "<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\" width=\"100%\" ><tr>";

        // display results
        while ($list = mysql_fetch_array($result1)) {
        $user_postcode = $list['value'];
        $distance = $list['distance'];
        $distance = round($distance);

   echo "<td align=\"right\">$distance miles away</td></tr>";
   echo "<tr><td colspan=\"3\" align=\"left\" width=\"130px\">$user_postcode </td>";
   echo "<td align=\"right\"><a href=\"blahblah\">Directions using Google Maps</a></td>";
   echo "<tr><td align=\"left\">&nbsp;</td><tr>";
        echo "</tr>";
}
echo "</table>";


Code: ("wp_bp_xprofile_data") [Select]
id field_id user_id value
1 1          1 admin
8 3          3 NW10
6 1          3 Test User 1
9 4          3 IT, Web Development
11 1          4 Test User 2
12 3          4 HA5
13 4          4 Test, It, Some Work
Code: ("postcodes") [Select]
outcode x y latitude longitude
B10 392900 804900 57 -2
AB11 394500 805300 57 -2
AB12 393300 801100 57 -2
AB13 385600 801900 57 -2
AB14 383600 801100 57 -2


Here is an example of what i need:

field_id "3" corresponds to a postcode. My script so far, can search a postcode and return results with the postcodes sorted by distance. What i'm trying to do, is also get the username on the result (i.e the 'value' column again..).

in short, im trying to search a postcode, find it on wp_bp_xprofile_data, associate it with the correspondent user, and return both.  Someone told me i need to INNER JOIN, but im lost..



The end result im looking for is if i type the search: "NW9 1AA"

It returns:

Test User 1
NW10
 

Test User 2
HA5   
(the code sorts them by distance. So far i can get it to return the postcode, but cannot associate the relevant name.

Can anyone help me with this?
I hope this is in the right forum.

Thanks for your attention.

whats the differance between

mysql_query("UPDATE banned SET time

and doing

$safe4 = "UPDATE banned SET time
mysql_query($safe4);


is there a reason as to why do it the 2nd way i mentioned?

if someone could please explain it to me so i wont be in confusion on this anymore

This topic has been moved to MySQL Help.

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

The first query goes through but the second does not just seeing if I'm missing something.

<?php

// Include the database page
require ('../inc/dbconfig.php');

if (isset($_POST['submittitle'])) {
    $titlename = mysqli_real_escape_string($dbc, $_POST['titlename']);
    $titleshortname = mysqli_real_escape_string($dbc, $_POST['titleshortname']);
    $titlestyle = mysqli_real_escape_string($dbc, $_POST['titlestyle']);
    $titlestatus = mysqli_real_escape_string($dbc, $_POST['titlestatus']);

    $query = "INSERT INTO `titles` (titlename, titleshortname, style_id, status_id, creator_id, datecreated) VALUES ('$titlename','$titleshortname','$titlestyle','$titlestatus', 1, NOW())";
    mysqli_query($dbc, $query);
    $query_id = mysqli_insert_id($dbc); 
    $query1 = "INSERT INTO `champions` (title_id) VALUES ('$query_id')";
    mysqli_query($dbc, $query1);  
}
?>

Hi all,

I've built a few amateur sites in the past but am keen to utilize php combined with mysql for my current project.  However, I have little knowledge of php and am just picking it up as I go (same way I've done for all my computer knowledge)

What I'm looking for may sound simple, but I really don't know where to start and I'm hoping there are some kind souls here that will lend a hand.  I've got some fantastic help here before and am hoping I'll get lucky again.

SQL Database
meetingid venue
001 new york
002 chicago
003 new york
004 new york
005 new york
006 texas
007 texas
008 chicago
009 new york
010 new york

Question
If I hard code everything around it, I'm just looking for the code I would need to have it display as follows: (hard coded text in blue)

"New York has been used * times." 
(Where * would count the number of times "New York" appears in the venue column of the database.)
"The most consecutive times New York has been used is % ." 
(Where % would count the maximum number of consecutive times "New York" appears in the venue column of the database.)

Giving the output:
New York has been used 6 times.
The most consecutive times New York has been used is 3.

UPDATE: If I remove lastedit = NOW() from the query, and just leave SET content = $var, it works. :/

If already double checked to see if a user would make it to this point in the code, and they do. And the variable $pid IS assigned a value. I've also done a quick check and changed the UPDATE queries to SELECT queries, and then echoed out the number of rows and it returned a successful result. Yet, people still can't seem to have their posts updated...why?

My code:

//if it's a thread, update the thread, if it's a post, update the post
if($type == 2)
{
//update thread
mysql_query("UPDATE threads SET lastedit = NOW() AND content = '{$content}' WHERE id = {$id}") or die(mysql_error());

//send them to their post
redirect('viewthread.php?forum='. $forum_id .'&id='. $id .'&page='. $page);
}
else
{
//update post
mysql_query("UPDATE posts SET lastedit = NOW() AND content = '{$content}' WHERE id = {$pid}") or die(mysql_error());

//send them to their post
redirect('viewthread.php?forum='. $forum_id .'&id='. $id .'&page='. $page .'&post='. $post);
}

I was wondering if it is possible to perform the following query.  Say I have a list of names each with an id number ranging from 1 to 10.  Is it possible to perform a query where you list all of these records while repeating one records.  For example could I do a query that lists record number 6 then 10, 9, 8, 7, 6 etc.  Or another example would be listing record number 10, then 10, 9, 8, 7, 6 etc?

Thanks for any help. 

Hello

I have this query and it is working well:
Code: [Select]
$data = mysql_query("
SELECT `title`, `body`, 'condoms_en' as REF FROM `condoms_en`
UNION
SELECT `title`, `body`, 'discr_en' as REF FROM `discr_en`
UNION
SELECT `title`, `body`, 'diseases_en' as REF FROM `diseases_en`
UNION
SELECT `title`, `body`, 'express_en' as REF FROM `express_en`
UNION
SELECT `title`, `body`, 'gender_en' as REF FROM `gender_en`
UNION
SELECT `title`, `body`, 'get_informed_en' as REF FROM `get_informed_en`
UNION
SELECT `title`, `body`, 'get_tested_en' as REF FROM `get_tested_en`
UNION
SELECT `title`, `body`, 'newly_en' as REF FROM `newly_en`
UNION
SELECT `title`, `body`, 'news_hiv_en' as REF FROM `news_hiv_en`
UNION
SELECT `title`, `body`, 'nutrition_en' as REF FROM `nutrition_en`
UNION
SELECT `title`, `body`, 'phdp_en' as REF FROM `phdp_en`
UNION
SELECT `title`, `body`, 'press' as REF FROM `press`
UNION
SELECT `title`, `body`, 'r_o_en' as REF FROM `r_o_en`
UNION
SELECT `title`, `body`, 'stats_en' as REF FROM `stats_en`
UNION
SELECT `title`, `body`, 'stigma_en' as REF FROM `stigma_en`
UNION
SELECT `title`, `body`, 'think_news_en' as REF FROM `think_news_en`
UNION
SELECT `title`, `body`, 'think_reports' as REF FROM `think_reports`
UNION
SELECT `title`, `body`, 'treatment_en' as REF FROM `treatment_en`
where`title` like '%$kw%' OR `body` like '%$kw%'") or die(mysql_error());
$rows = mysql_num_rows($data);

I need to add to the same query this part:
Code: [Select]
SELECT `cont`, 'about_us_en' as REF FROM `about_us_en`
UNION
SELECT `que`,`ans`, 'faq_en' as REF FROM `faq_en`
UNION
SELECT `orga`, `lnk`, 'links_en' as REF FROM `links_en`
UNION
SELECT `title`, `desc`, 'publications' as REF FROM `publications`

IF I add this part as is, I get an error saying that the select has different columns.

Please Help and how do I modify the where if they are combined?!

Thank you

Hi everyone

I have the following PHP code

$sql.= "INSERT INTO `data` (`info`, `write`, `date`) VALUES";
$sql.= "(";
        for ($c=0; $c < $num; $c++) {
$sql.= '"'.str_replace('"', "", $data[$c]).'",';
        }
$sql.= "'".date('Y-m-d')."');";

If I

print $sql;

I get

Code: [Select]
INSERT INTO `data` (`info`, `write`, `date`) VALUES("data99","n",'2010-10-05');
INSERT INTO `data` (`info`, `write`, `date`) VALUES("data101","y",'2010-10-05');
INSERT INTO `data` (`info`, `write`, `date`) VALUES("data876","n",'2010-10-05');
what would I need to do to my PHP code in order for it to process each QUERY either all at once or one at a time

Whatever I try, it either inserts nothing, or just does the first INSERT

Any ideas?

Thanks

I've been playing around with PDO lately, and I've been trying to get use to the basic functions as I've mentioned in some of my other posts/questions. Recently I switched to the ? bind/token (can't remember it's called), so I reformatted my processQuery. I've also tried to add in a fetching option in the method, but I can't seem to get anything to work.

I've looked up several internet tutorials but it seems like I'm doing everything the same. Yet, the query doesn't seem to run. :/

$database->processQuery2("INSERT INTO test (test, testa) VALUES (?, ?)", array('noob', 'newb2'), false);

Now for my method:


public function processQuery2($query, array $binds, $fetch)
    {
        $query_handle = $this->dbc->prepare($query);
        
        if(count($binds) > 0)
        {
            $i = 1;
            $value = array();
            
            foreach($binds as $bind)
            {
                $query_handle->bindParam($i, $value[$i]);
                $value[$i] = $bind;
                $i++;
            }
        }
        $query_handle->execute();
        
        //if($fetch == true)
        //{
            //return $query_handle->fetchAll();
        //}
    }


Any reasons to why it's not working? No errors are outputted, either.

Can any boy clarify these queries.
How do I find out if an array has values posted to each of its elements? I need to know that EVERY element has been filled out.
How can I send variables from a PHP script to another URL using POST without using forms and hidden variables?

   // Check for this userID if exsist in cp_credits
   $sql = "SELECT * FROM `cp_credits` WHERE account_id = $userid";
   
   // Execute Query
   $r = mysqli_query($dbc, $sql);
   
   
   // If there are result in $r
   if($r){
      // Print a message indicating success or not:
      while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)){
      $user_balance           = $row['balance'];
      $user_last_donation_date = $row['last_donation_date'];
   }
   }
   else{
   $user_balance = "nothing";
   $user_last_donation_date = "nothing";
   }

but either of my else statement does not work. Whats wrong with this?

Hi all

I have a site with 4 SQL queries that I need to paginate so that it returns 10 hits per page maximum. Basically there will be varying values of adverts in each SQL table.

Here's my code:

Code: [Select]
<!-- Get Trader premium adverts -->
   
   <?php 
 $gettraderads = mysql_query("SELECT * FROM `trade-adverts` WHERE type = 'premium' AND paid = 1 AND categoryid = 1 AND live = 1 AND approved = 1 AND dateexpired >= '".$todaysdate."' ORDER BY id DESC ");
 $numtraderads = mysql_num_rows($gettraderads);
 while ($showtraderads = mysql_fetch_array($gettraderads)) {
include('trader-ad-cell.php'); 
 }
?>
   
    <!-- Get Trader standard adverts -->
   
   <?php 
 $gettraderads = mysql_query("SELECT * FROM `trade-adverts` WHERE type = 'standard' AND categoryid = 1 AND live = 1 AND approved = 1 AND dateexpired >= '".$todaysdate."' ORDER BY id DESC ");
 $numtraderads = mysql_num_rows($gettraderads);
 while ($showtraderads = mysql_fetch_array($gettraderads)) {
include('trader-ad-cell.php'); 
 }
?>
   
    <!-- Get premium adverts -->
   
    <?php 
 $getads = mysql_query("SELECT * FROM `adverts` WHERE categoryid = 1 AND type = 'premium' AND paid = 1 AND live = 1 AND approved = 1 AND dateexpired >= '".$todaysdate."' ORDER BY id DESC ");
 while ($showads = mysql_fetch_array($getads)) {
 include('ad-cell.php'); 
 }
?>
   
    <!-- Get standard adverts -->
   
    <?php 
 $getads = mysql_query("SELECT * FROM `adverts` WHERE categoryid = 1 AND type = 'standard' AND live = 1 AND approved = 1 AND dateexpired >= '".$todaysdate."' ORDER BY id DESC ");
 while ($showads = mysql_fetch_array($getads)) {
 include('standard-ad-cell.php'); 
 }
?>


Many thanks for your help

Pete

The whole code is below, followed by the part that isn't producing.  It all works extremely well until the last part, and I have two instances of setting a variable to mysql_fetch_assoc that work.  Not sure why the third attempt doesn't.  I'm just trying to have a separate area that outputs 'msg'.

Code: [Select]

$sql = "SELECT * FROM players as p
INNER JOIN schools as s
WHERE p.tid = s.id
ORDER BY status, playerLast";

$results = mysql_query($sql);



echo '<div class="roster">';

$team = mysql_fetch_assoc($results);

echo '<div class="team_info">
<div class="school">' . $team['school'] . '</div>
<div class="coach">Coach ' .$team['coachFirst'] . ' ' . $team['coachLast'] .'</div>
<div>Sectional: ' . $team['sectional'] . '</div>
<div>Class: ' . $team['class'] . 'A</div>
';

echo '</div>';


  $currentStatus = false;  //Flag to detect change in status
    while($player = mysql_fetch_assoc($results))
    {


        if($currentStatus != $player['status'])
        {
            //Status has changed, display status header
            $currentStatus = $player['status'];
echo '<br><b>';
if ($currentStatus == '1')  {echo 'Returning Starters';}
elseif ($currentStatus == '2')  {echo 'Key Returners';}
elseif ($currentStatus == '3')  {echo 'Varsity Newcomers';}
elseif ($currentStatus == '4')  {echo 'Key Freshmen';}
echo '</b><br>';
           // echo "<div><b>{$currentStatus}</b></div>\n";
        }
        //Display player info
        echo $player['playerFirst'] . ' ' . $player['playerLast'] . ', ' . $player['feet'] . '\'' . $player['inches'] . '",' . $player['position'] . ', ' . $player['year'] . ';<br>';
 
    }

        //Player comments
echo '<hr>';
echo '<div class="coach_comments"><span class="player_name">hello';

while($comments = mysql_fetch_assoc($results)) {


echo $comments['playerFisrt'] . ' ' . $comments['playerLast'] . ':  </span>' . $comments['msg'];
}
echo '</div>';
echo '</div>';



It's all working until what's below.  All I get out of it is the "hello", which is just a test to make sure it wasn't a CSS issue.

Quote


        //Player comments
   echo '<hr>';
   echo '<div class="coach_comments"><span class="player_name">hello';
   
   while($comments = mysql_fetch_assoc($results)) {

      
      echo $comments['playerFisrt'] . ' ' . $comments['playerLast'] . ':  </span>' . $comments['msg'];
   }
echo '</div>';
echo '</div>';

I need two queries cause I don't know how to put them into one. Problem here is I need to know the field Liters from the database in the first query to subtract it from the second query to be able to calculate the average fuel consumption of the chosen range.   This is my code, hope you can help

function getContent() {
	include 'connection.php';

$dx = date('m', strtotime('today - 30 days'));	

	if (!isset($_POST["month_select"])) {
		$month = $dx;
		$year = "2014";
	}
	else {
		$month = $_POST["month_select"];
		$year = $_POST["year_select"];
	}

//last fuel fill value 
		$query2 = "SELECT d.liter as FirstFill 
  FROM members AS m
LEFT OUTER
  JOIN diesel AS d
    ON d.userid = m.id
   WHERE YEAR(dato) = $year AND MONTH(dato) = $month AND d.liter > 0 ORDER BY d.id ASC LIMIT 1";

		$sql2=$oDB->prepare($query2);
		$sql2->execute();
		$row2 = $sql2->fetchAll();
    return $row2;
	
    $FirstFill = $row2['FirstFill'];
	
		$query = "SELECT m.username, m.id, 10 * (SUM(d.liter) - $FirstFill) / (MAX(d.km) - MIN(d.km)) as AvgFuel 
  FROM members AS m
LEFT OUTER
  JOIN diesel AS d
    ON d.userid = m.id
   WHERE YEAR(dato) = $year AND MONTH(dato) = $month AND d.liter > 0
GROUP
    BY m.id ORDER BY AvgFuel ASC";
	
		$sql=$oDB->prepare($query);
		$sql->execute();
		$row = $sql->fetchAll();
    return $row;

	}

Edited by Aero77, 23 May 2014 - 12:39 PM.