sql syntax error

hi dears i have a trouble problem in my query, please help me thanks for helping."You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'deff_month month) as b from teacher_hours_late, teacher where teacher.teacher_' at line 1"

  $this->db->select("teacher.teacher_id,name,lname,father_name,salary_per_hour,monthly_salary,"
                        . "(select ($user_month - month(salary_start_date)) from teacher)as deff_month ,"
                        . "(select ADDDATE(salary_start_date, interval deff_month month) from teacher) as start_interval,"
                        . " (select ADDDATE(start_interval, interval 1 month)) as end_interval ,"
                        . "(select DATE_SUB(start_interval, interval 1 month)) as reverse_end_interval,"
                        . "(select date($miladi)) as user_date,(select sum(hours) from teacher_hours_late where (date>start_interval AND date<=end_interval AND start_interval<$miladi ) OR (date<=start_interval AND date>reverse_end_interval AND start_interval>=$miladi )) as total_late");
                $this->db->from('teacher');
                $this->db->join('teacher_hours_late',"teacher.teacher_id=teacher_hours_late.teacher_id", 'inner');

                $this->db->where('teacher.branch_id',$branch_id);
                $query=$this->db->get();
                return $query->result();

Select All MySQL Rows

I have the following code which choose all rows in MYSQL but it show the last row only

    $query = mysql_query("SELECT * FROM `users`") or die(mysql_error());
     while ( $row = mysql_fetch_assoc($query) )
          {
          $token = $row['instagram_access_token'];
          }
          echo "$token";

example for passing list object parameters into mysql procedure through hibernate

I am Searching for, Passing List bean objects into mysql Procedure , using spring and hibernate, Then how to use that list object inside mysql procedure, please provide any example.

MySql adaptive IDs

I have a table set up with an auto increasing ID. Lets say I have the IDs 1, 2, 3, 4 and 5. When I remove ID number 3, I want ID 4 to drop down to 3, and ID 5 to drop down to 4.

Is this possible? How is this done?

How to disable % from outputting everything

Hey I have an search field where I am searching something from my database, now I saw the problem after testing that if I put "%" in the search field it will output everything that I have ready for searching. Is there a way to disable this?

<h3>Search Share Details</h3>
<p>You may search either by company name or issue date</p>

<form name = "search" method = "get">
<input type = "text" name = "share" size = "40" maxlength="50">
<input type = "submit" value = "Search">
</form>

Getting contents connecting to DB, fetching results and printing

function get_contents() {

   if(isset($_GET['share']))
    {
        $conn = db_connect();
        $shares = get_shareSearch($conn);
        db_disconnect($conn);
        $contents = array('shares' => $shares);
        return $contents;
    }
    else
    {
        $conn = db_connect();
        $shares = get_share($conn);
        db_disconnect($conn);
        $contents = array('shares' => $shares);
        return $contents;
    }
}


function print_contents($contents) 
{

    if(count($contents['shares']) == 0)
    {
        echo "<script type = 'text/javascript'>alert('Sorry but share is not found! Q_Q');</script>";

    }
    else
    {
    ?>   
        <table>
            <tr>
                <th>Company Name</th>
                <th>Rate</th>
                <th>Issue Date</th>

            </tr>
    <?php
    foreach ($contents['shares'] as $share) 
    {
        print "<tr>";
        $identifier = urlencode($share['SHAREID']);
        print "<td><a href='share-details.php?id={$identifier}'>{$share['COMPANY']}</a></td>";
        print "<td>{$share['RATE']}</td>";

        $issue_date = $share['ISSUE_DATE'];
        $issue_date = $issue_date === NULL ? "&lt; not available &gt;" : $issue_date;
        print "<td>{$issue_date}</td>";
        print "</tr>";
    }
    ?>
        </table>
    <?php
    }
}
//require("shares.php");
require("search.php");
?>

Query itself

function get_shareSearch($conn) { 
$id = "";
if(isset($_GET['share'])){$id = $_GET['share'];}
$statement = db_create_statement($conn, "SELECT DISTINCT * FROM shares  WHERE(company LIKE '{$id}' OR issue_date LIKE '{$id}')");
$resultset = db_fetch_resultset($statement); 
return $resultset; 

}

php mysql fetch two table

i have two table from mysql.

1, photo (url_link)

2, video(url_link)

i wanna fetch only 1 table (video url) if both table aren't empty.. Is there anyways? am using code below:

<--photo -->
    <?php       
$query_img = mysql_query("SELECT `url` FROM `photos_news` WHERE title='{$thumn_content['titles']}' LIMIT 0,1");
while($img = mysql_fetch_array($query_img)){ ?>
<img src="<?php echo $img['url'];?>" class="img-responsive img-rounded"/>
<?php }?>


<!-- vdo -->
<?php
$query_vdo = mysql_query("SELECT `url` FROM `video_news` WHERE title='{$thumn_content['titles']}' LIMIT 0,1");
while($vdo = mysql_fetch_array($query_vdo)){ ?>
<div class="embed-responsive embed-responsive-16by9">
  <iframe class="embed-responsive-item" src="<?php echo $vdo['url']?>?modestbranding=1&showinfo=0&fs=0"></iframe>
</div>

<?php }?>

thank you sir,

MySQL: Select unique field and return all other columns as well + if specific column is not 0 - return that

I can't figure out how to get this done. Basically I am making message system, I have this table:

Basically I am trying to print out threads, like every distinct msgfrom is a new thread. Thread should basically say:

[(Sender Name) (Last Message (ORDER BY id DESC)) ([if at least one message has msgread=0 then say You have unread messages])

I've tried this so far:

SELECT id, msgfrom, message, date, 
IF(msgread='0', 'unread', 'read') AS msr 
FROM pmessages WHERE msgto=$userId GROUP BY msgfrom

But that returns:

message from: Username (this is msgfrom 1)
read
message from: Username (this is msgfrom 2)
unread

Even though msgfrom1 has 1 unread message, it says read (I assume it takes first result). So in basic:

SELECT UNIQUE msgrom
ORDER BY id DESC
IF at least ONE msgread = 0
   return * + set msgread=0 (unread)

It's a bit hard to explain, sorry if it's a bit unclear.

xampp control panel Apache port is not opening

    i am new to php. i have downloaded xampp control panel and installed it but when i run the control panel.it showing this error.please help me.
i have changed port id and PID also but same problem.

i have deleted total xampp and installed fro3 times till ow but same problem. [Apache] You need to uninstall/disable/reconfigure the blocking application [Apache] or reconfigure Apache and the Control Panel to listen on a different port [Apache] Problem detected! [Apache] Port 443 in use by "Unable to open process" with PID 1444! [Apache] Apache WILL NOT start without the configured ports free! [Apache] You need to uninstall/disable/reconfigure the blocking application [Apache] or reconfigure Apache and the Control Panel to listen on a different port.

PDO says it updates the table, but it actually doesn't

I'm trying to use a form to update a sql table by first getting its data (editrower.php) and setting that as values in the form, then using the form to update the table (update_contactrequest.php) but it returns saying the rower was updated yet the table does not update.

editrower.php

<?php
require('login.php');
?>
        <?php
        include 'php/mysql_connect.php';
        if(isset($_GET['id'])){
        $q = $db->prepare('SELECT * FROM rowercontacts WHERE id=:id LIMIT 1');
        $q->execute(array(':id'=>$_GET['id']));
        $row = $q->fetch(PDO::FETCH_ASSOC);
        if($row){
        echo '
      <form method="post" action="php/update_contactrequest.php"><div class="col-xs-9 col-md-6 col-lg-6">
        <div class="form-group">
          <input type="hidden" name="id" id="id" value="'.$_GET['id'].'">
          <label for="firstname">First Name</label>
          <input type"text" class="form-control" name="firstname" placeholder="First Name" value="'.$row['firstname'].'" />
        </div>
        <div class="form-group">
          <label for="lastname">Last Name</label>
          <input type="text" class="form-control" name="lastname" placeholder="Last Name" value="'.$row['lastname'].'" />
        </div>
        <div class="form-group">
          <label for="address">Home Address</label>
          <input type="text" class="form-control" name="address" placeholder="Address" value="'.$row['address'].'" />
        </div>
        <div class="form-group">
          <label for="city">City</label>
          <input type="text" class="form-control" name="city" placeholder="City" value="'.$row['city'].'" />
        </div>
        <div class="form-group">
          <label for="zip">Zip Code</label>
          <input type="text" class="form-control" name="zip" placeholder="Zip" value="'.$row['zip'].'" />
        </div>
        <div class="form-group">
          <label for="email">Email Address</label>
          <input type="email" class="form-control" name="email" placeholder="Your Email" value="'.$row['email'].'" />
        </div>
        <div class="form-group">
          <label for="cellphone">Cell Phone Number</label>
          <input type="text" class="form-control" name="cell" placeholder="Cell Number" value="'.$row['cell'].'" />
        </div>
        <div class="form-group">
          <label for="homephone">Home Phone Number</label>
          <input type="text" class="form-control" name="home" placeholder="Home Phone Number" value="'.$row['home'].'"/>
        </div>
        <br><br>
        <div>        
          <h3>Parent/Guardian Information</h3>
          <br>
        </div>
        <h4>Parent/Guardian 1</h4>
        <div class="form-group">
          <label>Name</label>
          <input type="text" class="form-control" name="p1name" placeholder="Guardian 1 Name" value="'.$row['p1name'].'" />
        </div>
        <div class="form-group">
          <label>Email Address</label>
          <input type="email" class="form-control" name="p1email" placeholder="Guardian 1 Email" value="'.$row['p1email'].'" />
        </div>
        <div class="form-group">
          <label>Phone</label>
          <input type="text" class="form-control" name="p1phone" placeholder="Guardian 1 Phone" value="'.$row['p1phone'].'" />
        </div>
        <div class="form-group">
          <label>Occupation</label>
          <input type="text" class="form-control" name="p1occupation" placeholder="Guardian 1 Occupation" value="'.$row['p1occupation'].'" />
        </div>
        <div class="form-group">
          <label>Relation</label>
          <input type="text" class="form-control" name="p1relation" placeholder="mother, father, etc." value="'.$row['p1relation'].'" />
        </div>
        <br>
        <h4>Parent/Guardian 2</h4>
        <div class="form-group">
          <label>Name</label>
          <input type="text" class="form-control" name="p2name" placeholder="Guardian 2 Name" value="'.$row['p2name'].'" />
        </div>
        <div class="form-group">
          <label>Email Address</label>
          <input type="email" class="form-control" name="p2email" placeholder="Guardian 2 Email" value="'.$row['p2email'].'" />
        </div>
        <div class="form-group">
          <label>Phone</label>
          <input type="text" class="form-control" name="p2phone" placeholder="Guardian 2 Phone" value="'.$row['p2phone'].'" />
        </div>
        <div class="form-group">
          <label>Occupation</label>
          <input type="text" class="form-control" name="p2occupation" placeholder="Guardian 2 Occupation" value="'.$row['p2occupation'].'" />
        </div>
        <div class="form-group">
          <label>Relation</label>
          <input type="text" class="form-control" name="p2relation" placeholder="mother, father, etc." value="'.$row['p2relation'].'" />
        </div>
        <br>
        <div class="form-group">
          <textarea class="form-control" rows="3" name="extrainformation" placeholder="Additional Info (parent work phone numbers, health information, etc.)">'.$row['extrainformation'].'</textarea>
        </div>
        <br>
        <small>Any additional parents/guardians who wish to be notified of updates can request under the "contact" section</small>
        <br><br>
        <input type="submit" class="btn btn-default" value="Update" />
      </div></form>
        ';
      }
      else{
      echo 'No rower found';
      }
      }
      else{
        echo 'No rower found';
      }
      ?>

update_contactrequest.php:

<?php
session_start();
if($_SESSION['loggedIn'] == true){

$rower_id= $_POST['id'];
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$address = $_POST['address'];
$city = $_POST['city'];
$zip = $_POST['zip'];
$email = $_POST['email'];
$cell = $_POST['cell'];
$home = $_POST['home'];
$p1name = $_POST['p1name'];
$p1email = $_POST['p1email'];
$p1phone = $_POST['p1phone'];
$p1occupation = $_POST['p1occupation'];
$p1relation = $_POST['p1relation'];
$p2name = $_POST['p2name'];
$p2email = $_POST['p2email'];
$p2phone = $_POST['p2phone'];
$p2occupation = $_POST['p2occupation'];
$p2relation = $_POST['p2relation'];
$extrainformation = $_POST['extrainformation'];
  // connection to the database
try {
$bdd = new PDO('mysql:host=localhost;dbname=home','username','password');
} catch(Exception $e) {
exit('Unable to connect to database.');
}

// insert the records
$sql = "UPDATE rowercontacts SET firstname=:firstname, lastname=:lastname, address=:address, city=:city, zip=:zip, email=:email, cell=:cell, home=:home, p1name=:p1name, p1email=:p1email, p1phone=:p1phone, p1occupation=:p1occupation, p1relation=:p1relation, p2name=:p2name, p2email=:p2email, p2phone=:p2phone, p2occupation=:p2occupation, p2relation=:p2relation, extrainformation=:extrainformation WHERE id=:rower_id";
$q = $bdd->prepare($sql);
if($q->execute(array(':firstname'=>$firstname, ':lastname'=>$lastname, ':address'=>$address, ':city'=>$city, ':zip'=>$zip, ':email'=>$email, ':cell'=>$cell, ':home'=>$home, ':p1name'=>$p1name, ':p1email'=>$p1email, ':p1phone'=>$p1phone, ':p1occupation'=>$p1occupation, ':p1relation'=>$p1relation, ':p2name'=>$p2name, ':p2email'=>$p2email, ':p2phone'=>$p2phone, ':p2occupation'=>$p2occupation, ':p2relation'=>$p2relation, ':extrainformation'=>$extrainformation, ':rower_id'=>$id))){
  echo '<script type="text/javascript">alert("Rower Updated.");location.href="../rowerlist.php";</script>';
}
else{
  echo '<script type="text/javascript">alert("Something went wrong...");location.href="../rowerlist.php";</script>';
}

}
?>

Using HTTP Session VS serialized JSON array to store an SQL result

I have a bunch of virtual host subdomains that use the same source code. Each subdomain has a row in a settings table that has the settings for the subdomain. These settings determine things like what html template to load, age rating and around 30 other settings so they are vital. Rather than query the DB every single time a page is loaded, I want to find a way to save the data for the duration of the session to reduce the load on the DB.

If i use sessions i can query the DB if the session doesnt exist and save the contents of the query to the session where i can then use the values on each page load to determine how the page should be displayed. The question here is will a huge amount of session variables for all users create a performance hit?

Alternatively i was thinking i could query the DB and save the contents of the array to a file as JSON.I could then read the file upon each page load to get the settings. This could be refreshed every hour or so and could be shared across all users on each subdomain. The issue here is will a massive amount of requests to a single json file hurt disk performance?

Deleting database records MVC

I've built a MVC 'framework' for learning purposes, and I'm struggling with this problem:

I am working on a CRUD application and I don't know how I should delete the records of my database, right now I'am doing it thru URL.

http://ift.tt/1GuzK8c that is how I delete a record of a database. But I don't really like this way, because anyone could unintentionally delete database records or intentionally do it, so my question is, how should I implement this feature?

Thank you in advance!

Select ___where __LIKE ___

I want to select all the tables which doesn't contain the character '_' from a database, for this I used the request :

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema="DB_NAME" and TABLE_NAME NOT LIKE '%_%' ; 

And it doesnt seem to work . How can proceed ?

MySQL: Count then sort by the count total

I know other posts talk about this, but I haven't been able to apply anything to this situation.

This is what I have so far. "SELECT * FROM ccParts, ccChild, ccFamily WHERE parGC ='26' AND parChild = chiId AND chiFamily = famId ORDER BY famName, chiName"

What I need to do is see the total number of ccParts with the same ccFamily in the results. Then, sort by the total.

How do I find non-xml characters in mysql data column

I have a database where a java program is erroring out because there are non-xml characters in the mysql database. I would like to know how to do a regex to find the records that have non-valid XML characters. I could not find a query to run anywhere on the web.

CHMOD 777 on Directories for Database to be Able to Write (Help Me Understand)

I have a limited understanding of MySQL's interaction with my CentOS 7 server and need some help.

Basically, I have a WordPress installation and I need to set the uploads directory and all directories within to CHMOD 777 to upload images via the backend. CHMOD 775 doensn't work though. This baffled me and is something I am not willing to do as I unserstand the risks and how easily it can be exploited. I would be giving public write permissions in that folder and that is bad right?

My database user has privileges to the database and the database connects. The website works minus writing part. Now what I don't understand is how the MySQL user relates to the local CentOS user at all. I don't believe that is linked anywhere and if I am wrong how would i do that?

The reason I am thinking this is due to the CHMOD 775 not working meaning that the said database user is not a user or a member of the group required to have said access and to be able to write. Hence why I need local write permissions enabled.

===================================================

MORE INFO

Local User Directory: /home - CHMOD 755, CHOWN root:root

Local Users /home/usr1 - CHMOD 501, CHOWN usr1:usr1 ,/home/usr2 - CHMOD 501, CHOWN usr2:usr2

Contains Website WWW: /home/usr1/www - CHMOD 775, CHOWN usr1:usr1 ,/home/usr2/www - CHMOD 775, CHOWN usr2:usr2

Rest of Files/Directories: CHOWN usr1:usr1 for all files in WWW ,CHOWN usr2:usr2 for all files in his respective WWW

CHOWN 775 for all directories in WWW

,CHOWN 664 for all files in WWW

So when I connect the database to the wordpress installation, how in the heck does it know to use root, usr1 or usr2. I tried putting them all in a group to see what happens but it didn't work. Thought it was worth a shot.

Any help, advice, education and so and so forth is appreciated. Help me if you can please :)

==================================================

I have access via SSH, I am on a VPS and as far as I know have complete access. So if you have a solution that requires me to do stuff, that is cool. I can do stuff!

code need to be fix it

code need to be fix it my query is working all is good i have diffrent dates,rooms,count datefrom and count rooms in rows but $maxrooms is is not working any suggestions

$maxRooms1 =4;
$maxRooms2 =5;
$maxRooms3 = 5;
        
dates are dates between start and end dates to make clear.

   

   $fetchQuery = " SELECT dates, rooms, COUNT( dates ) , COUNT( rooms ) 
FROM calendar3
GROUP BY rooms, dates";         
    
      
   
   $fetchResult = mysql_query($fetchQuery);
    $count = mysql_num_rows($fetchResult)and $dates = mysql_num_rows($fetchResult);
        
         
         
         if ($row['COUNT(rooms1)'] and $row['COUNT(dates)']   <=  $maxRooms1       ){
    echo "</br>"."1  bed is not available!";
} elseif ($row['COUNT(rooms1)'] and $row['COUNT(dates)']   >  $maxRooms1        ) {
   echo "</br>"."1  bed is  available !";

}
   if ($row['COUNT(rooms2)'] and $row['COUNT(dates)']   <=  $maxRooms2       ){
    echo "</br>"."2  bed is not available!";
} elseif ($row['COUNT(rooms2)'] and $row['COUNT(dates)']   >  $maxRooms2        ) {
   echo "</br>"."2  bed is  available !";

} 
   
       if ($row['COUNT(rooms3)'] and $row['COUNT(dates)']   <=  $maxRooms3       ){
    echo "</br>"."3  bed is not available!";
} elseif ($row['COUNT(rooms3)'] and $row['COUNT(dates)']   >  $maxRooms3        ) {
   echo "</br>"."3  bed is  available !";
} 
    

table structure for table calendar3 -- CREATE TABLE calendar3 ( id int(11) NOT NULL AUTO_INCREMENT, dates date NOT NULL, dateto date NOT NULL, rooms int(11) NOT NULL, KEY id (id) ) ENGINE=InnoDB AUTO_INCREMENT=276 DEFAULT CHARSET=latin1 AUTO_INCREMENT=276 ; -- -- Dumping data for table calendar3 -- INSERT INTO calendar3 VALUES (272, '2015-12-12', '0000-00-00', 1); INSERT INTO calendar3 VALUES (273, '2015-12-13', '0000-00-00', 1); –

mysql procedure call with out parameter not working in PHP

I have created a test procedure (for the sake of example to get this working) called test_proc like below -

    DELIMITER $$

    CREATE DEFINER=`tloken`@`%` PROCEDURE `test_proc`(In user_id varchar(100), OUT message varchar(1000))
    BEGIN

    set message ='OK';
    END

and calling it from php test page like below -

$sso = $_GET['sso'];
$ret = '';
echo $RepExecutQry = "call test_proc(?, ?)";

$stmt = $conn->Prepare($RepExecutQry);


$stmt->bindParam(1, $sso, PDO::PARAM_INT, 10);
$stmt->bindParam(2, $ret, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 32);

$stmt->execute();

print "Values of bound parameters _after_ CALL:\n";
print "  1: {$ret} 2: {$sso}\n"     

Also tried the solution with bindValue(), but the procedure doesn't return anything. $conn is a pdo connection object and created properly, tested with var_dump($conn) already. Sorry to create the duplicate question, but all other suggestions on the forum for similar problem I tried already and they were not working. any suggestions would be valued and appreciated. Thank you

How to compare length of data in mysql type text using php?

i have this table name = text_tab

 _________________________
|  id  |     message     |
|  1   |     aaa         |
|  2   |     bb          |
|  3   |     ffff        |
|  4   |     ooooooo     |
|  5   |     gg          |

I want to check row that data in column message have max char. And column message is type text.

in my answer will show id=4

I use order by message desc but not work, How can i do that ?

facing on Making voting system (Like /Unlike) Button for Q&A website in php , mysql using ajax

i have a Question & Answer website as a part of my graduation project , so am still fresh to such languages but i will try to be specific as much as i can ,,, well i got voting for both Question and answers at each special question page ,, i use the same voting code with changing queries to retrieve desired data ,, but the problem is one of the voting system only works (Question voting or Answers voting) i suppose the problem is action listener is listen to the same btn or the span or smth like that i tried to change values but without any mentioned result,, i will provide all the code am using and am ready to replay to any comment to make stuff clear so i hope you guys help me out to make both voting systems work in the same page ,, thnx you very much ... ^^

srip.js > script for questions

 $(document).ready(function(){
    // ajax setup
$.ajaxSetup({
    url: 'ajaxvote.php',
    type: 'POST',
    cache: 'false'
});

// any voting button (up/down) clicked event
$('.vote').click(function(){
    var self = $(this); // cache $this
    var action = self.data('action'); // grab action data up/down 
    var parent = self.parent().parent(); // grab grand parent .item
    var postid = parent.data('postid'); // grab post id from data-postid
    var score = parent.data('score'); // grab score form data-score

    // only works where is no disabled class
    if (!parent.hasClass('.disabled')) {
        // vote up action
        if (action == 'up') {
            // increase vote score and color to orange
            parent.find('.vote-score').html(++score).css({'color':'orange'});
            // change vote up button color to orange
            self.css({'color':'orange'});
            // send ajax request with post id & action
            $.ajax({data: {'postid' : postid, 'action' : 'up'}});
        }
        // voting down action
        else if (action == 'down'){
            // decrease vote score and color to red
            parent.find('.vote-score').html(--score).css({'color':'red'});
            // change vote up button color to red
            self.css({'color':'red'});
            // send ajax request
            $.ajax({data: {'postid' : postid, 'action' : 'down'}});
        };

        // add disabled class with .item
        parent.addClass('.disabled');
       };
   });
});

ajaxvote.php for opertion inside the questions

<?php


include('config.php');
# start new session
dbConnect();
session_start(); /*  changes will occuar here */

if ($_SERVER['HTTP_X_REQUESTED_WITH']) {
if (isset($_POST['postid']) AND isset($_POST['action'])) {
    $postId = (int) mysql_real_escape_string($_POST['postid']);
    # check if already voted, if found voted then return
    //if (isset($_SESSION['vote'][$postId])) return;
    # connect mysql db
    dbConnect();

    # query into db table to know current voting score 
    $query = mysql_query(" 
        SELECT rate
        from qa
        WHERE id = '{$postId}' 
        LIMIT 1" ); /*  changes will occuar here */

    # increase or dicrease voting score
    if ($data = mysql_fetch_array($query)) {
        if ($_POST['action'] === 'up'){
            $vote = ++$data['rate'];
        } else {
            $vote = --$data['rate'];
        }
        # update new voting score
        mysql_query("
            UPDATE qa
            SET rate = '{$vote}'
            WHERE id = '{$postId}' "); /*  changes will occuar here */

        # set session with post id as true
        $_SESSION['vote'][$postId] = true;
        # close db connection
        dbConnect(false);
    }
}
}
?>

printing code : QR.php

  <?php 

require ("coonection.php");

if(isset($_GET['start']) )
 {
  $FURL = $_GET['start'];


   $data=mysql_query("SELECT * FROM qa WHERE id=($FURL)");
   while($d=mysql_fetch_assoc($data)) { ?>
  <div class="item" data-postid="<?php echo $d['id'] ?>"  data-score="<?php echo $d['rate'] ?>">
        <div class="vote-span"><!-- voting-->
            <div class="vote" data-action="up" title="Vote up">
                <i class="glyphicon glyphicon-thumbs-up"></i>
            </div><!--vote up-->
            <div class="vote-score"><?php echo $d['rate'] ?></div>
            <div class="vote" data-action="down" title="Vote down">
                <i class="glyphicon glyphicon-thumbs-down"></i>
            </div><!--vote down-->
        </div>

        <div class="title"><!-- post data -->
              <p><?php echo $d['question'] ?></p>
          </div>
     </div><!--item-->
    <?php  } } ?>
    </div>

 </p>


                        </div>
    <div class="single-post-title" align="center">
    <h2>Answers</h2>
    </div>
                        <!-- Comments -->
     <?php



  require ("coonection.php");
    if(isset($_GET['start']) )
    {
        $FURL = $_GET['start'];
        $data=mysql_query("SELECT * FROM answers WHERE question_id=($FURL)");
        while($d = mysql_fetch_assoc($data))
        {



                echo'<div class="shop-item">';
                echo'<ul class="post-comments">';
                echo'<li>';
                echo'<div class="comment-wrapper">';
                echo'<h3>';
                echo  $d['answerer'] ;
                echo'</h3>';
                echo '</div>'; ?>
                 <div class="item" data-postid="<?php echo $d['answer_id'] ?>" data-score="<?php echo $d['rate'] ?>">
        <div class="vote-span"><!-- voting-->
            <div class="vote" data-action="up" title="Vote up">
                <i class="icon-chevron-up"></i>
            </div><!--vote up-->
            <div class="vote-score"><?php echo $d['rate'] ?></div>
            <div class="vote" data-action="down" title="Vote down">
                <i class="icon-chevron-down"></i>
            </div><!--vote down-->
        </div>

        <div class="post"><!-- post data -->
            <p><?php echo $d['answer'] ?></p>
        </div>
    </div><!--item-->
<?php
                echo'<div class="comment-actions"> <span class="comment-date">';
                echo  $d['dnt'] ;
                echo'</div>';
                echo'</li>';
                echo'</ul>';
                echo'</div>';            




          }

        }
    ?>

i got ajaxvote2.php and also got scrip2.js for settings in answer ,,, i think using the same code make the printing page confused and only listen to one of voting systems

i will add ajaxvote2.php and scrip2.js just in case some one need to look at them ...

ajaxvote2.php

 <?php


include('config.php');
 # start new session
 dbConnect();
 session_start(); /*  changes will occuar here */

 if ($_SERVER['HTTP_X_REQUESTED_WITH']) {
 if (isset($_POST['postid']) AND isset($_POST['action'])) {
    $postId = (int) mysql_real_escape_string($_POST['postid']);
    # check if already voted, if found voted then return
    //if (isset($_SESSION['vote'][$postId])) return;
    # connect mysql db
    dbConnect();

    # query into db table to know current voting score 
    $query = mysql_query(" 
        SELECT rate
        from answers
        WHERE answer_id = '{$postId}' 
        LIMIT 1" ); /*  changes will occuar here */

    # increase or dicrease voting score
    if ($data = mysql_fetch_array($query)) {
        if ($_POST['action'] === 'up'){
            $vote = ++$data['rate'];
        } else {
            $vote = --$data['rate'];
        }
        # update new voting score
        mysql_query("
            UPDATE answers
            SET rate = '{$vote}'
            WHERE answer_id = '{$postId}' "); /*  changes will occuar here */

        # set session with post id as true
        $_SESSION['vote'][$postId] = true;
        # close db connection
        dbConnect(false);
    }
}
} 
?>

scrip2.js

$(document).ready(function(){
// ajax setup
$.ajaxSetup({
    url: 'ajaxvote2.php',
    type: 'POST',
    cache: 'false'
});

// any voting button (up/down) clicked event
$('.vote').click(function(){
    var self = $(this); // cache $this
    var action = self.data('action'); // grab action data up/down 
    var parent = self.parent().parent(); // grab grand parent .item
    var postid = parent.data('postid'); // grab post id from data-postid
    var score = parent.data('score'); // grab score form data-score

    // only works where is no disabled class
    if (!parent.hasClass('.disabled')) {
        // vote up action
        if (action == 'up') {
            // increase vote score and color to orange
            parent.find('.vote-score').html(++score).css({'color':'orange'});
            // change vote up button color to orange
            self.css({'color':'orange'});
            // send ajax request with post id & action
            $.ajax({data: {'postid' : postid, 'action' : 'up'}});
        }
        // voting down action
        else if (action == 'down'){
            // decrease vote score and color to red
            parent.find('.vote-score').html(--score).css({'color':'red'});
            // change vote up button color to red
            self.css({'color':'red'});
            // send ajax request
            $.ajax({data: {'postid' : postid, 'action' : 'down'}});
        };

        // add disabled class with .item
        parent.addClass('.disabled');
    };
 });
});

DB Table of constants vs constants in code

Eternal question.

I have a table moments_of_day:

id name
1  Morning
2  Afternoon
3  Evening
4  Night

And then an other table, meals, references it with moments_of_day_id.

And then, in my PHP code I want to get all meals of morning.

What's the best option? Should I repeat constants in my PHP MomentsOfDay model doing something like next code?

const MORNING = 1;

By repeating constants I think it can be dangerous. If in the future the table changes I also have to add it to the MomentsOfDay model.

What do you think?

PHP/MySQLi: Commands out of sync; you can't run this command now

I am coding a PHP/MySQL(i) website application, and am getting the dreaded error, "Commands out of sync; you can't run this command now".

I have a mysqli connection, and have multiple queries to run on the page.

(ps - This is different from a similar-titled question, that attempts to open a second $mysqli->query() while the first is still active...in my case, I am closing one then opening a second)

Here is what I am doing:

<?php
$sql = 'CALL grc.spSelectProductAndVendor(1, 1)'; 
$rs = $conn->query($sql);
echo($conn->error);
if ($rs->num_rows > 0) {
    while ($row=$rs->fetch_assoc()) {
        echo '<h1 class="page-header">' . $row["productname"] . '<small> pertaining to vendor: ' . $row["vendorname"] . '</small></h1>';
    }   
}   
$rs->close();
?>

HTML/CSS code.....

<?php
$sql = 'CALL grc.spSelectRecentDocumentScores();';
$rs = $conn->query($sql);
echo($conn->error);
if ($rs->num_rows > 0) {
    while ($row=$rs->fetch_assoc()) {
        if($row["totalwarnings"]>50) { echo '<tr class="danger">'; } else { echo '<tr class="active">'; }
?>
<td><a href="documentdetail.php?documentid=<?php echo $row["documentid"]; ?>" style="color:#000000"><?php echo $row["documentid"]; ?></a></td>
<td>etc...</td>
</tr>
<?php
    }
}
?>

Question is, why does this error only occur upon the SECOND resultset call?

$rs = $conn->query($sql);

The first one works fine. The second does not. But I am closing the first $rs.

Any thoughts?

Thank you.....

error on php code line 17 please heelp [on hold]

please i dont know what is wrong with my code on line 17
<?php
include "scripts/connection.php";
    //error_reporting(0);
        $email = $_POST['email'];
        $username = $_POST['username'];
        $password = md5($_POST['password']. "AL552KAO09");
         $confPassword = md5($_POST['confPassword']. "AL552KAO09");
     echo"text";
  if (isset($email, $username, $password, $confPassword)){
    if (strstr($email, "@")){
        if ($password == $confPassword){
                $query = $dbc->prepare('SELECT * FROM users WHERE username = ? OR email = ?');
                $query = $query->execute(array(
                        $username,
                        $email
                        ));
                $count = mysql_num_rows($query); //this is the line 17 Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\wamp\www\my php\work\register.php on line 17
                if($count == 0){
                
                        $query = $dbc->prepare('INSERT INTO users SET username= ? , email= ?, passowrd= ?');
                        $query = $query->execute(array(
                                $username,
                                $email,
                                $password
                                ));
                        if($query){
                                echo "you have been registered succesfully plese click here to login";
                        }
            }else{
                echo "user already exist with that username";
                }
        }else{
                echo "password is not matching";
        }
    }else{
        echo "invalid email address";
        }
    }
?>
<html>
        <head>
                <form action="" method="POST">
                <table border="0" width="500" align="center" cellpadding="5px">

    <tr> 
     <Td colspan="2" style="background:#993300;color:#FFF">
       Login
    </td>
     </tr>
   
<tr>
     <td>
       email
    </td>
     <td>
        <input type="text" name="email"/>
     </td>
    </tr>



<tr>
     <td>
      username
    </td>
     <td>
        <input type="text" name="username"/>
     </td>
    </tr>


 <tr>
        
        <tr>
     <td>
      password
    </td>
     <td>
        <input type="password" name="password"/>
     </td>
    </tr>
    <tr>
     <td>
      confirm_password
    </td>
     <td>
        <input type="password" name="confPassword"/>
     </td>
    </tr>


 <tr>


 <tr>
<td colspan="2">
  <center>
   <input type="submit" name="submit" value="Register"/>
</center>
 </td>
  </tr>

 <tr>
  <td colspan="2">
                <form>

        </head>
</html>

mysql database send alert email by date and time

I am trying to create a auto email that will send an email once the date and time have been met in the mysql database. The following fields are needed name, email address, date and time of notification to be sent, account number, notes. I have created my HTML Form, PHP to enter the data and the mysql database. I have searched all over to find where to find code for the looping search of date and time.

HTML Code

<title>Reminder Email</title>

<style type="text/css">
.style166 {color: #FFFFFF}
</style>

</head>

<body>

<form name="reminder_email" action="reminder.php" method="post">
<table id="reminder_email">
<tbody>
<tr>
    <td bgcolor="#0000FF"><div align="center" class="style166">Reminder Email</div></td>
    </tr>
    <tr><td width="235" rowspan="5" valign="top" style="border:1px solid black; border-color:black;">
    <div align="left">Name</div>
    <div align="left">
    <input type="text" name="name" size="30">
    </div>
<br/>
    <div align="left">Email Address</div>
    <div align="left">
    <input type="text" name="name" size="30">
    </div>
<br/>
    <div align="left">Alert Date</div>
    <div align="left">
    <input type="date" name="date" size="30">
    <div align="left">Format MM/DD/YYYY</div>
</div>
<br/>
    <div align="left">Alert Time</div>
    <div align="left">
    <input type="time" name="time" size="30">
    <div align="left">Please use military time.</div>
</div>
<br/>
<div>
    <div align="left">ESL Ticket Number</div>
    <div align="left">
    <input type="text" name="eslticket" size="30">
</div>
<br/>
    <div align="left">Notes:</div>
    <div align="left">
    <textarea cols="25" rows="5" name="notes"></textarea>
</div>
<br/>
<div>
</form>  
    <div align="center">
    <input type="submit" value="Submit">
    <input type="reset" value="Clear">
    </div></td>
</div></tbody>
</body></html>

PHP Code

<?php

define('DB_NAME', 'reminder');
define('DB_USER', 'reminder');
define('DB_PASSWORD', '');
define('DB_HOST', 'localhost');

$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);

if (!link) {
    die('Could not connect: ' . mysql_error());
}

$db_selected = mysql_select_db(DB_NAME, $link);

if (!db_selected) {
    die('Can\'t use ' . DB_NAME . ' : ' . msql_error());
}

$name = $_POST['name'];
$email = $_POST['email'];
$date = $_POST['date'];
$time = $_POST['time'];
$eslticket = $_POST['eslticket'];
$notes = $_POST['notes'];





$sql = "INSERT INTO alert (name, email, date, time, eslticket, notes) VALUES ('$name', '$email', '$date', '$time', '$eslticket', '$notes')";

if (!mysql_query($sql)) {
    die('Error: ' . mysql_error());
}

mysql_close();

echo "<script type='text/javascript'>alert('Alert Entered.');</script>";

?>

<meta http-equiv="refresh" content="0;url=http://ift.tt/1KgnfvH">

How to specify sort order on multiple self joins

I've an attribute table like

CREATE TABLE attributes (
    attribute_id INT,
    product_id INT,
    random INT,
    UNIQUE KEY (attribute_id,random,product_id),
    KEY (product_id)
);

random is a random integer number calculated on insert for shuffling products (that's O.K. for my needs). There are self-join queries like

SELECT DISTINCT x.product_id
FROM attibutes x
INNER JOIN attributes y ON x.product_id=y.product_id
INNER JOIN attributes z ON x.product_id=z.product_id
WHERE x.attribute_id IN (20000085,20000090) AND
    y.attribute_id IN (10000007) AND
    z.attribute_id IN (30000050,30000040,30000012)
LIMIT 0,100;

As you can see I want to select products which have at least one attribue in each number range. MySQL is so clever to choose table alias for first query itself, depending on selectivity of UNIQUE key. As expected the result is sorted in order of column random because of UNIQUE key. But how can I advise MySQL to revert the order? When adding ORDER BY x.random DESC it could happen that MySQL uses filesort for ordering because if it uses table alias y for base query (because of better selectivity of attribute ID 10000007) it has to use UNIQUE key of alias x. The problem is: I don't know which alias MySQL does use (it's decided by its query optimizer). So how to specify order direction?

(I want to note that table contains about 60 million rows, so usage of filesort or not would be significant in response time)

How to escape apostrophe in php variable for select query

i have tried escaping the qurey string and the $variable containing the apostrophe using the mysqli_real_escape_string the variable value is coming form the database. i am getting the following error.

You have an error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use 
near '\'Shamrock Rovers%\' AND away_team like \'St Patrick's Athletic%\'' at line 1

the apostrophe is not geiiting escaped byt the quotes around the comparison vales is.

here is the query as it appers in the PHP file:

    $homeTeam = filter_input(INPUT_GET, 'homeTeam', FILTER_SANITIZE_STRING);
    $homePlayers = "select * from players where team_name like $homeTeam";
    $homePlayers = mysqli_real_escape_string($dbc, $homePlayers);
    $homePlayersResult = mysqli_query($dbc, $homePlayers);

And echoed out to the browser:

    select * from players where team_name like Shamrock Rovers

I have tried it a number of different ways with no variation in results i feel i am overlooking something simple. Thanks in advance.

How To Add Google Map Markers From Mysql Database Only Using Address With Geocoder

I am trying to create some code that pulls an address from a mysql database with JSON. This works. However I cant finish the script that pulls the JSON array and adds markers to a google map with the Geocoder API. Please note this should work without lat and lon; it adds markers purely with just an address

Here is the code for the JSON mysql array:

require 'config.php';

try {

    $db = new PDO($dsn, $username, $password);
    $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

    $sth = $db->query("SELECT * FROM peopleforjobs");
    $locations = $sth->fetchAll();

    echo json_encode( $locations );

} catch (Exception $e) {
    echo $e->getMessage();
}

And here is the javascript on the main page where the map is shown:

  function makeRequest(url, callback) {
var request;
if (window.XMLHttpRequest) {
    request = new XMLHttpRequest(); // IE7+, Firefox, Chrome, Opera, Safari
} else {
    request = new ActiveXObject("Microsoft.XMLHTTP"); // IE6, IE5
}
request.onreadystatechange = function() {
    if (request.readyState == 4 && request.status == 200) {
        callback(request);
    }
}
request.open("GET", url, true);
request.send();
}
var map;

// Ban Jelačić Square - City Center
var center = new google.maps.LatLng(45.812897, 15.97706);

var geocoder = new google.maps.Geocoder();
var infowindow = new google.maps.InfoWindow();

function init() {

var mapOptions = {
  zoom: 13,
  center: center,
  mapTypeId: google.maps.MapTypeId.ROADMAP
}

map = new google.maps.Map(document.getElementById("map_canvas"), mapOptions);

makeRequest('get_locations.php', function(data) {

    var data = JSON.parse(data.responseText);

    for (var i = 0; i < data.length; i++) {
        displayLocation(data[i]);
    }
});
}

if (geocoder) {
geocoder.geocode({
  'address': location.address
}, function(results, status) {
  if (status == google.maps.GeocoderStatus.OK) {
    if (status != google.maps.GeocoderStatus.ZERO_RESULTS) {
      map.setCenter(results[0].geometry.location);

      var infowindow = new google.maps.InfoWindow({
        content: '<b>' + location.address + '</b>',
        size: new google.maps.Size(150, 50)
      });

      var marker = new google.maps.Marker({
        position: results[0].geometry.location,
        map: map,
        title: location.address
      });
      google.maps.event.addListener(marker, 'click', function() {
        infowindow.open(map, marker);
      });

    } else {
      alert("No results found");
    }
  } else {
    alert("Geocode was not successful for the following reason: " + status);
  }
 });
}

It Throws An Error: "ZERO_RESULTS" Does anybody know why?

Parse error: syntax error, unexpected T_STRING on line 12 [duplicate]

This question already has an answer here:

• PHP Parse/Syntax Errors; and How to solve them? 9 answers

Hello all I ve a problem with php editing so my problem is:

Parse error: syntax error, unexpected 'select' (T_STRING) in C:\Program Files (x86)\EasyPHP-DevServer-14.1VC11\data\localweb\projects\toppvp.php on line 12

and here is my php file

<

    ?php

    $db_user = "root";
    $db_pass = "";
    $db_name_gs = "l2jgs";
    $db_name_ls = "l2jls";
    $db_name_cs = "l2jcs";
    $res = mysql_connect ( $db_serv, $db_user, $db_pass ) or die ("Coudn't connect to [$db_serv]");
        $resdb = mysql_select_db ( $db_name_gs, $res ) or die (mysql_error("Cannot connect to Game Server"));
        $resdb = mysql_select_db ( $db_name_ls, $res ) or die (mysql_error("Cannot connect to Login Server"));
        $resdb = mysql_select_db ( $db_name_cs, $res ) or die (mysql_error("Cannot connect to Community Server));
    $res="select characters.char_name,characters.pvpkills,char_templates.ClassName,characters.online,characters.accesslevel from characters,char_templates where characters.classid=char_templates.Classid order by characters.pvpkills DESC LIMIT 20";
    echo"<html><head></head><body bgcolor='#000000' style='color:rgb(200,200,200)'>";
    echo "<table  border='2' align=center width=440>";
    echo "<tr><th>Nr</th><th>Name</th><th>PvP Kills</th><th>Main class</th><th>Status</th></tr>\n";
    if ($result=mysqli_query($con, $sql)or die("Bed Sql syntax")) {
      $nr=1;
    while ($row=mysql_fetch_row($result)) {
         echo "<tr><td align=center>".$nr."</td>";
         $nr++;
        echo "<td align=center>".$row[0]."</td>";
        echo "<td align=center>".$row[1]."</td>";
        echo "<td align=center>".$row[2]."</td>";
        if($row[4]==0)
        {   
            if($row[3])
            {echo "<td align=center style='color:rgb(0,255,0)'>Online</td></tr>\n"; }
             else{echo "<td align=center style='color:rgb(255,0,0)'>Offline</td></tr>\n";}
        }
         else{echo "<td align=center style='color:rgb(255,0,0)'>Hidden</td></tr>\n";}
    }
    }else{ echo "<!-- SQL Error ".mysql_error()." -->";}

    echo "</table></body></html>";


    ?>

I searched 2 hours to find an solution and i saw same question like mine on this forum but didnt find a suitable one :(

Would appreciate if someone help me Thank You,

Php code to import excel file to database

I want to import Excel data to my database using PHPExcel. Below is my code.

When I upload the excel file and submit the form, I can echo out the table but no data is being stored in the database, Can anyone please help me with this.

<?php

# Database Connection
$dbc = mysqli_connect('localhost', 'root', 'ron143', 'tcc') OR die('Error: '.mysqli_connect_error());

/** Include path **/
#set_include_path(get_include_path() . PATH_SEPARATOR . 'PHPExcel/Classes/');

/** PHPExcel_IOFactory */
include 'PHPExcel/Classes/PHPExcel.php';

/** PHPExcel_IOFactory */
include 'PHPExcel/Classes/PHPExcel/IOFactory.php';

# Upload the file to server
$file = $_FILES["file"]["name"];
$target_dir = "../../temp/";
$target_file = $target_dir . basename($file);
move_uploaded_file($_FILES["file"]["tmp_name"], $target_file);
$xls_url = "$target_dir$file";

$inputFileName = $xls_url;
$inputFileType = $_POST['file_type'];
$sheetname = '0'; /** input the worksheet number to read. 0 for first and 1 for second worksheet */

/**  Create a new Reader of the type defined in $inputFileType  **/
$objReader = PHPExcel_IOFactory::createReader($inputFileType);

/**  Advise the Reader that we only want to load cell data and not its formating or any formula set on it **/
$objReader->setReadDataOnly(true);

/**  Load $inputFileName to a PHPExcel Object  **/
$objPHPExcel = $objReader->load($inputFileName);

//  Get worksheet dimensions
$objWorksheet = $objPHPExcel->getSheet($sheetname);
$highestRow = $objWorksheet->getHighestRow(); // e.g. 10
$highestColumn = $objWorksheet->getHighestColumn(); // e.g 'F'

$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); // e.g. 5

echo '<table>' . "\n";
for ($row = 2; $row <= $highestRow; ++$row) {
    echo '<tr>' . "\n";

for ($col = 0; $col <= $highestColumnIndex; ++$col) {

    echo '<td>' . $objWorksheet->getCellByColumnAndRow($col, $row)->getValue() . '</td>' . "\n";

  }
  echo '</tr>' . "\n";
}
  echo '</tr>' . "\n";

#$q = "INSERT INTO products (product_name, product_code, category1, category1_id, category2, category2_id, stylename, grams, thickness, width, length, color_ground, color_border, material, backing, reed, weave, ply, pile, care, precaution, use, tag, image, link) VALUES(`".$val[0]."`, `".$val[1]."`, `".$val[2]."`, `".$val[3]."`, `".$val[4]."`, `".$val[5]."`, `".$val[6]."`, `".$val[7]."`, `".$val[8]."`, `".$val[9]."`, `".$val[10]."`, `".$val[11]."`, `".$val[12]."`, `".$val[13]."`, `".$val[14]."`, `".$val[15]."`, `".$val[16]."`, `".$val[17]."`, `".$val[18]."`, `".$val[19]."`, `".$val[20]."`, `".$val[21]."`, `".$val[22]."`, `".$val[23]."`, `".$val[24]."`)";
#$r = mysqli_query($dbc, $q);

if($q!='') $q.=',';
$q.='(\''.mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colproduct_name, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colproduct_code, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colcategory1, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colcategory1_id, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colcategory2, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colcategory2_id, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colstylename, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colgrams, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colthickness, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colwidth, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($collength, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colcolor_ground, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colcolor_border, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colmaterial, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colbacking, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colreed, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colweave, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colply, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colpile, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colcare, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colprecaution, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($coluse, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($coltag, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colimage, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($collink, $row)->getValue()).'\')';

if($q!='') {
    $q='INSERT INTO products (product_name, product_code, category1, category1_id, category2, category2_id, stylename, grams, thickness, width, length, color_ground, color_border, material, backing, reed, weave, ply, pile, care, precaution, use, tag, image, link) VALUES '.$q;
    $r=mysqli_query($dbc, $q);
    }


?>

SET PASSWORD Error

I am trying to reset my root password using following command:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('test');

and it gives me following error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD('test')' at line 1

Please let me know what is it that I am doing wrong here.

Thanks,

PS: I am new to this so pardon my lack of knowledge. Also, I am trying to do this on my Mac if that matters.

How to compare multiple table columns with multiple values?

I have a table with N columns like this

---

column 1 | column 2 | column 3 | ...column N

---

I have n values like this

---

value 1 , value 2 , value 3 , ...value n

---

I wanted to compare these n values with my table's N columns. One way I can do it is:

Select * from table where

column 1 IN (value 1 , value 2 , value 3 , ...value n)
||
column 2 IN (value 1 , value 2 , value 3 , ...value n)
||
column 3 IN (value 1 , value 2 , value 3 , ...value n)
||
.
.
.
column n IN (value1 , value1 , value1 , ...value n)

Is this the most fastest way to do it?

Compare data from two tables and check the same data

Hello I have two tables in my database.

The first one is table disease which look like the above...

And the second is table patient which look like this ...

And I have an edit page where I want to give the user the ability to update his/her diseases. An example is the below..

What I want to do is to check the disease column from the table patient with the column name from the table disease and check that if a data from the table patient is the same with the data from the table disease then check from the checkboxes the same disease.

I tried to find a way to do it by I couldnt Here is my code...

<?php
$sql = "SELECT name FROM disease UNION SELECT disease FROM patient WHERE username='$username'";
$query_resource = mysql_query($sql);

  while( $name = mysql_fetch_assoc($query_resource) ):
?>
    <span><?php echo $name['name']; ?></span>
    <input type="checkbox" name="disease[]" value="<?php echo $name['name']; ?>" /><br />


<?php endwhile; ?>

The way I store the diseases in my database is this...

$disease = implode(",",$_POST["disease"]);

Because a user might have many diseases

I have the same error message when i put any error in the file

when I submit this form i have the same error message all the time . even if i put right or wrong password or don't put password or i write the name of the data base wrong . all of this wrongs i have the same error message : Please enter a username and password . so what is the problem . and i am sure about my fields on data base .

    <?

    session_start();
    $username = $_POST['username'];
    $password = $_POST['password'];


    if ($username && $password)
    {

        $connect = mysql_connect("localhsost","root","adminffpass") or die("Couldent connet to database ");
        mysql_select_db("login") or die("No data base found ");

        $query = mysql_query("SELECT * FROM users WHERE username='$username'");

        $numrows = mysql_num_rows($query);

        if ($numrows !=0)
        {

            while ($row= mysql_fetch_array($query)) 
            {

                $dbusername = $row['username'];
                $dbpassword = $row['password'];

            }

            if ($username == $dbusername && $password==$dbpassword)
            {
                echo "Login successul .<a href='memeberarea.php'>Click to enter member area</a>";
                $_SESSION['username'] = $dbusername;
            }
            else
                echo "incorrect  password  ";

        }
        else
           die ("That user name dosent exist");

   }
   else
     die ("Please enter a username and password");


    ?>

Users in my social network can't register and login

I made a simple social network system, with register and login function, but whem the user register and try to login, he give this message:

Fatal error: Call to a member function rowcount() on a non-object in home/login.php on line 7

There is some problem in my login.php code?:

<?php
include('dbcon.php');
$username = $_POST['username'];
$password = $_POST['password'];

$query = $conn->query("select * from database where username = '$username' and password = '$password'");
$count = $query->rowcount();
$row = $query->fetch();
if ($count > 0){
session_start();
$_SESSION['id'] = $row['member_id'];
 header('location:home.php'); 
}else{
 header('location:index.php'); 
}
?>

SQL for showing row data in columns

Here is my sql (in mysql table)

select * from(SELECT sample_register.usin,    
                     DATE_FORMAT(sample_register.doc,'%d-%m-%Y') as doc1,    
                     sample_register.location,    
                     sample_register.description,    
                     sample_register.type,    
                     sample_allocation.gamma,    
                     gamma_results.act,    
                     gamma_results.act_sd,    
                     gamma_results.mdl,    
                     gamma_results.bdl,    
                     DATE_FORMAT(count_dt,'%d-%m-%Y') as count_dt    
               FROM sample_register    
               LEFT JOIN sample_allocation    
               ON sample_register.usin=sample_allocation.usin    
               LEFT JOIN gamma_results    
               ON gamma_results.usin = sample_register.usin    
               AND gamma_results.istp='Cs137'    
               WHERE mid(sample_register.usin,3,1)='F'    
               AND sample_register.doc BETWEEN '2015-01-01'    
                                       AND '2015-03-31'    
                                       AND sample_register.category='ter'    
                                       AND sample_allocation.gamma='Y'    
               ORDER BY mid(sample_register.usin,3,1),    
                        sample_register.doc,    
                        sample_register.usin) AS a    
               LEFT JOIN (SELECT sample_register.usin,    
                                 gamma_results.act,    
                                 gamma_results.act_sd,    
                                 gamma_results.mdl,    
                                 gamma_results.bdl    
                          FROM sample_register    
                          LEFT JOIN gamma_results    
                          ON gamma_results.usin = sample_register.usin    
                          AND gamma_results.istp='k40'    
                          WHERE mid(sample_register.usin,3,1)='F'    
                          AND sample_register.doc    
                          BETWEEN '2015-01-01'    
                          AND '2015-03-31'    
                          AND (sample_register.category='ter')    
                          ORDER BY mid(sample_register.usin,3,1),    
                                   sample_register.doc,    
                                   sample_register.usin) AS b    
               ON a.usin=b.usin

There are 4 records in the gamma_results table. two records each for 10/04/2015 and 18/04/2015.

USIN        istp     act     count_dt
-----------------------------------------
15FML002    Cs137   0.00769  10/04/15
15FML002    K40     0        10/04/15
15FML002    Cs137   0.00608  18/04/15
15FML002    K40     12.117   18/04/15

Query output data in the following form (some fields I deleted for convenience)

15FML002            0.00769 Y   10/04/15    00
15FML002            0.00769 Y   10/04/15    12.117
15FML002            0.00608 Y   18/04/15    00
15FML002            0.00608 Y   18/04/15    12.117

But I want to get output in two records. That is like this

15FML002            0.00769 Y   10/04/15    00
15FML002            0.00608 Y   18/04/15    12.117

How can I reframe (join or union) the query to get output like this?

MySQL Server 5.7 installation fails at "Writing configuration file"

I'm trying to install MySQL Server 5.7.7 on Windows Server 2008 machine. It has failed several times and I tried uninstalling the whole thing and try the installation again. I tried installing one component at a time and it worked for all the components (Workbench, connectors,..etc.) except for the MySQL Server itself.

I'm receiving error at the step "Writing configuration file", the log shows the error details below:

Beginning configuration step: Writing configuration file
Could not find a part of the path 'C:\ProgramData\MySQL\MySQL Server 5.7\my.ini'.
Ended configuration step: Writing configuration file

Display count of unique values in MySQL db table using PHP

So I have a table named pins that has 3 columns that need to be taken into consideration. These columns are plan and order_id.

I need to get a count for all of the pins that have an order_id=0 and plan=9.

This is what I have so far:

$qT="SELECT plan, COUNT(*) as cnt FROM pins WHERE order_id=0 and plan=9";
$res=mysql_query($qT);<br/>
mysql_free_result($res);

while($row = mysql_fetch_array($res)) {<br/>
echo $row['plan'];<br/>
}

Any help in displaying the results would be a great help.

how to make a multi delete query

Hello people i have a question about this query;

DELETE FROM 
 our_work, 
 our_work_images, 
 our_work_portf_img
WHERE 
 our_work.id = 6
AND 
 our_work_portf_img.portf_id = 6
AND
 our_work_portf_img.img_id = our_work_images.img_id;

i want to delete all relations from the first table "our_work" my pair table is named "our_wrok_porft_img" and my image table "our_work_images"

how to obtain the following output from a single table

Table Name: Employee
S_No    Heading  Summary
 1       a         A
 2       b         B
 3       c         C
 4       d         D

I want to display it as:

S_No  Heading  Summary   S_No1  Heading  Summary2
1       a        A         2       b         B
3       c        C         4       d         D

Progress 4GL connecting to MySQL using libmysql.dll

I am trying to connect from Progress OpenEdge to MySQL using libmysql.dll and mysql-functions.i which I found online.

It works connecting from Progress OpenEdge 101b to MySql, but when I try to connect from Progress OpenEdge 1102 to MySql it crashes Progress with the following Problem signature of APPCRASH and Exception code: c0000374.

I got the libmysql.dll from here: http://ift.tt/1PAkAjq

Here is the mysql-functions.i code:

define variable mysql-row    as character extent 1000.
define variable db_log_query as logical  no-undo init no.

function db_close returns integer (input mysql as memptr):

    run mysql_close(mysql).
    set-size(mysql) = 0.
    return 1.
end.

function db_connect returns integer (input-output mysql as memptr):

    define variable mysql_conn as memptr. 
    define variable mysql-host as character no-undo format "x(12)".
    define variable mysql-db   as character no-undo.
    define variable mysql-port as integer   no-undo.
    define variable mysql-user as character no-undo.
    define variable mysql-pass as character no-undo.

    set-size(mysql) = 952.
    set-size(mysql_conn) = 952. 

     assign mysql-host = "localhost"
            mysql-db   = ""
            mysql-port = 5002
            mysql-user = "root"
            mysql-pass = "".


    run mysql_init(0,output mysql).

    run mysql_real_connect(input-output mysql,mysql-host,mysql-user,
                      mysql-pass,mysql-db,mysql-port,"",0,output mysql_conn).
    set-size(mysql_conn) = 0.
    return 1.
end.

function db_last_error returns character (input mysql as memptr).
    define variable err-msg as character no-undo.
    define variable mysql_err as memptr no-undo.

    set-size(mysql_err) = 1000.
    run mysql_error(mysql,output mysql_err).
    err-msg = replace(get-string(mysql_err,1),"'","\\'").
    set-size(mysql_err) = 0.
    return err-msg.
end.                        


function db_fetch_into_array returns integer (input mysql_res as memptr):

    define variable num-fields as integer.
    define variable i          as integer.
    define variable mysql_row  as memptr.
    define variable mysql_element as memptr.
    define variable row_lengths   as memptr.
    define variable stat as integer.
    define variable field-lengths as integer no-undo extent 200.

    if get-size(mysql_res) = 0 then
        return -1.

    set-size(mysql_row) = 800.

    run mysql_num_fields(mysql_res,output num-fields).

    run mysql_fetch_row(mysql_res,output mysql_row).
    if get-pointer-value(mysql_row) = 0 then do:
        stat = -1.
        return stat.
    end.

    run mysql_fetch_lengths(mysql_res,output row_lengths).

    do i = 1 to num-fields:
        field-lengths[i] = get-long(row_lengths,i * 4 - 3).
    end.

    set-size(mysql_element) = field-lengths[i].

    do i = 1 to num-fields:
        set-pointer-value(mysql_element) = get-long(mysql_row, i * 4 - 3).
        if field-lengths[i] > 0 then
            mysql-row[i] = get-string(mysql_element,1,field-lengths[i]).
        else
            mysql-row[i] = "".
    end.
    set-size(mysql_element) = 0.
    set-size(mysql_row) = 0.
    set-size(row_lengths) = 0.

    return 1.
end.

function db_free_result returns integer (mysql_res as memptr):

    run mysql_free_result(mysql_res).
    set-size(mysql_res) = 0.
    return 1.
end.

function db_query returns integer(mysql as memptr,sql-query as character):

    define variable stat as integer.

    run mysql_query(mysql,sql-query,output stat).
    if sql-query begins "select" or db_log_query = no then
        return stat.

    define variable sql        as character no-undo.
    define variable log-sql    as character no-undo.
    define variable query-stat as integer   no-undo.
    define variable cur-table  as character no-undo.
    define variable err-msg    as character no-undo.
    define variable mysql_err  as memptr    no-undo.
    define variable mysql2     as memptr    no-undo.

    if sql-query begins "insert" or sql-query begins "delete" or
       sql-query begins "replace" then
        cur-table = entry(3,sql-query," ").
    else 
        cur-table = entry(2,sql-query," ").

    assign cur-table = entry(1,cur-table,"(")
           /* Now handled by mysql_real_escape_string 
           log-sql = replace(sql-query,"\\","") 
           log-sql   = replace(replace(log-sql,"'","\\'"),'"','\\"').
           */
           log-sql = sql-query.

    if stat <> 0 then do:
        set-size(mysql_err) = 1000.
        run mysql_error(mysql,output mysql_err).
        err-msg = replace(get-string(mysql_err,1),"'","\\'").
        set-size(mysql_err) = 0.
    end.

    sql = "SET SQL_LOG_BIN=0".
    db_connect(mysql2).
    run mysql_query(mysql2,sql,output query-stat).

    sql = "insert into utility.update_log" +
                "(user_id,sql,error,program_name,table_name) values('" +
                userid(ldbname(1)) + "','" +  /* will have to fix this */
                log-sql    + "','" +
                err-msg       + "','" +
                program-name(1)    + "','" +
                cur-table          + "')".

    run mysql_query(mysql2,sql,output query-stat).
    db_close(mysql2).
    return stat.
end.

function db_use_result returns memptr (input mysql as memptr):

    define variable mysql_res as memptr.
    set-size(mysql_res) = 84.

    run mysql_use_result(mysql,output mysql_res).

    return mysql_res.

end.

function db_escape_string return character (input mysql as memptr,
                                        input cFrom as character):

    define variable lcFrom as longchar  no-undo.
    define variable mFrom  as memptr    no-undo.
    define variable mTo    as memptr    no-undo.
    define variable iTo    as integer   no-undo.
    define variable cOut   as character no-undo.

    if get-size(mysql) = 0 then
        leave.

    assign lcFrom = cFrom.

    copy-lob from object lcFrom to mFrom no-error.

    set-size(mTo) = (get-size(mFrom) * 2) + 1 no-error.

    run mysql_real_escape_string(input  mysql,
                                 output mTo,
                                 input  mFrom,
                                 input  get-size(mFrom),
                                 output iTo) no-error.

    set-size(mTo)   = iTo no-error.
    assign cOut     = string(mTo).
    set-size(mFrom) = 0 no-error.
    set-size(mTo)   = 0 no-error.
    assign lcFrom   = "".

    return string(cOut).
end.

/********************** end public functions *******************/

procedure mysql-fetch-into-array:

    define variable num-fields as integer.
    define variable i          as integer.
    define variable mysql_row  as memptr.
    define variable mysql_element as memptr.
    define variable row_lengths as memptr.
    define variable field-lengths as integer no-undo extent 200.

    define input    parameter mysql_res as memptr.
    define output   parameter stat as integer.

    IF get-size(mysql_res) = 0 then do:
        assign stat = -1.
        return.
    end.

    set-size(mysql_row) = 800.
    set-size(row_lengths) = 1000.

    run mysql_num_fields(mysql_res,output num-fields).

    run mysql_fetch_row(mysql_res,output mysql_row).
    if get-pointer-value(mysql_row) = 0 then do:
        stat = -1.
        leave.
    end.

    run mysql_fetch_lengths(mysql_res,output row_lengths).

    do i = 1 to num-fields:
        field-lengths[i] = get-long(row_lengths,i * 4 - 3).
    end.

    do i = 1 to num-fields:
        /* Updates pointer size to include all the field */
        set-size(mysql_element) = field-lengths[i].
        set-pointer-value(mysql_element) = get-long(mysql_row, i * 4 - 3).
        mysql-row[i] = get-string(mysql_element,1,field-lengths[i]).
    end.
    set-size(mysql_element) = 0.
    set-size(mysql_row) = 0.
    set-size(row_lengths) = 0.
end.

procedure mysql-fetch-fields-into-array:

    /* note: this function needs to be fixed, see 
            mysql-fetch-into-array to fix */

    define variable num-fields as integer.
    define variable i          as integer.
    define variable mysql_field  as memptr.
    define variable mysql_element as memptr.
    define variable used-length as integer.
    define variable tmp-long as integer.

    define input    parameter mysql_res as memptr.
    define output   parameter stat as integer.

    if get-size(mysql_res) = 0 then do:
        assign stat = -1.
        return.
    end.

    set-size(mysql_field) = 100.

    run mysql_num_fields(mysql_res,output num-fields).

    set-size(mysql_element) = 2000.

    used-length = 0.
    do i = 1 to num-fields:
        run mysql_fetch_field(mysql_res,output mysql_field).
        tmp-long = get-long(mysql_field,1).
        set-pointer-value(mysql_element) = tmp-long.
        mysql-row[i] = get-string(mysql_element,1).
    end.
    set-size(mysql_element) = 0.
    set-size(mysql_field) = 0.
end.


procedure mysql_fetch_field external "libmysql.dll" persistent:
    define input  parameter mysql_res as memptr.
    define return parameter mysql_field as memptr.
end procedure.

procedure mysql_init external "libmysql.dll" persistent:

    define input parameter nothing as long.
    define return parameter mysql as memptr.

end.

procedure mysql_real_connect external "libmysql.dll" persistent: 

    define input-output parameter mysql       as memptr.
    define input parameter host        as character.
    define input parameter mysql-user  as character.
    define input parameter passwd      as character.
    define input parameter db          as character. 
    define input parameter port        as unsigned-short.
    define input parameter socket      as character.
    define input parameter client_flag as short.
    define return parameter stuff      as memptr.
end.

procedure mysql_error external "libmysql.dll" persistent:

    define input parameter mysql       as memptr.
    define return parameter my-err     as memptr.
end.

procedure mysql_close external "libmysql.dll" persistent:

    define input parameter mysql as memptr.

end procedure.

procedure mysql_query external "libmysql.dll" persistent:

    define input parameter mysql as memptr.
    define input parameter sql-query as character.
    define return parameter stat as short.

end procedure.

procedure mysql_use_result external "libmysql.dll" persistent:

    define input parameter mysql as memptr.
    define return parameter mysql_res as memptr.

end.

procedure mysql_fetch_row external "libmysql.dll" persistent:

    define input parameter mysql_res as memptr.
    define return parameter mysql_row as memptr.

end .

procedure mysql_num_fields external "libmysql.dll" persistent:

    define input parameter mysql_res as memptr.
    define return parameter num-rows as short.

end.

procedure mysql_fetch_lengths external "libmysql.dll" persistent:

    define input parameter mysql_res as memptr.
    define return parameter row_lengths as memptr.

end.

procedure mysql_free_result external "libmysql.dll" persistent:

    define input parameter mysql_res as memptr.

end.

procedure mysql_real_escape_string external "libmysql.dll" persistent:
    define input parameter mysql as memptr.
    define output parameter cTo as memptr.
    define input parameter cFrom as memptr.
    define input parameter iLength as long.
    define return parameter ierror as long.
end.

Can't remove whitespaces in string

My script reads txt file and then writes values into database table. There is a field with price, and in txt file price with 4 digit values have whitespace after first digit (2 500 ex.). I put this function (str_replace works well and replaced ',' but when I use it for removing whitespace it does not do it, and finally I put preg_replace but it also skips whitespace):

$model->price = preg_replace('/\s+/','',str_replace(',', '.', $row_data[3]));

datatype of price field in my table is varchar, when I used int or decimal it skips digits after space (2 070 becomes 2, 12 015 => 12). I have to remove this space in integration stage, because when I do operations with price field php skips digits after space and I have errors in my further manipulations.

What is the problem guys?

Select mysql data starting from a specific date

I have player statistics which I would like to publish from certain dates. At the moment I can see statistics in the database from the begining.

SELECT name, 
       bankmoney AS Bank,
       Playerkills AS 'Player Kills',
       deathcount AS Deaths ,
       aikills AS 'AI Kills',
       teamkills AS 'Team Kills',
       revivecount AS Revives ,
       capturecount AS 'Territories Captured',
       LastModified AS 'Last Seen'
FROM playerinfo
JOIN playerstats
ON playerinfo.UID = playerstats.PlayerUID
ORDER BY BankMoney DESC;

But I would like to present statistics from the start of the day and the start of the week.

How would I do that ?

Can't connect to mysql with Sequelize

I consistently get a SequelizeConnectionRefusedError when trying to connect to a MySQL database on my server. The login credentials are correct, the port is open, everything seems good (and works like a charm in the dev environment).

Sorry for the scarce background information, but I'm dumbfounded here - I really don't know what could be causing this problem.

This is my output from mysql --version

mysql  Ver 14.14 Distrib 5.5.43, for debian-linux-gnu (x86_64) using readline 6.3

And this is the code I'm using to initialize Sequelize. The table I want it to use doesn't exist yet, but I'm fairly sure that hasn't got anything to do with this problem. I've tried logging in with the root user as well, but no dice - I still get the same error.

var sequelize = new Sequelize("database", username, password, {
    host: "localhost",
    dialect: "mysql",
    port: 3306,
    define: {
        paranoid: true
    }
});

var Model = sequelize.define("Model", {
    md5: {type: Sequelize.STRING(128)},
    ip: {type: Sequelize.STRING(256)},
    url: {type: Sequelize.STRING(1024)}
});

sequelize.sync();

This is running on Ubuntu 14.04, where node is being run behind Passenger (although the error appears if I run the application with node directly as well). I'm running nginx and PHP on the same server, where another PHP application is connecting to the database, if that's of any relevance.

What could be causing this problem?

How to change the default port of mysql from 3306 to 3360

I want to change the default port number of mysql server presently it is 3306. I want to change it to 3360 I have tried

-- port=3360

But things are not working for me. Please provide query to change port not any configuration. I am using windows 8 -64 bi. I know this is not right forum to ask this question. but please don't downvote

Why is this MySQL query not using the complete index

Could you help me with this query, please?

SELECT p.patid, MAX(c1.eventdate) as eventdate 
from patient as p 
left join op_adv_effects._clinical as c1 on p.patid = c1.patid 
where c1.eventdate < p.case_index 
group by p.patid

Here is the output of SHOW CREATE TABLE for the 2 tables:

patient CREATE TABLE `patient` (
  `patid` int(10) unsigned NOT NULL,
  `case_index` date NOT NULL,
  `pracid` smallint(5) unsigned DEFAULT NULL,
  `first_registration_date` date DEFAULT NULL,
  `transfer_out_date` date DEFAULT NULL,
  `transfer_out_reason` varchar(100) COLLATE latin1_general_cs DEFAULT NULL,
  `last_collection_date` date DEFAULT NULL,
  `deathdate` date DEFAULT NULL,
  `birth_year` int(6) unsigned DEFAULT NULL,
  `gender` varchar(100) COLLATE latin1_general_cs,
  `marital_in_gprd` varchar(100) COLLATE latin1_general_cs,
  `strategic_health_authority` varchar(100) COLLATE latin1_general_cs,
  PRIMARY KEY (`patid`,`case_index`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs

_clinical   CREATE TABLE `_clinical` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `patid` int(10) unsigned NOT NULL,
  `eventdate` date NOT NULL,
  `sysdate` date DEFAULT NULL,
  `constype` tinyint(3) unsigned DEFAULT NULL,
  `consid` int(10) unsigned NOT NULL,
  `medcode` mediumint(8) unsigned DEFAULT NULL,
  `staffid` int(10) unsigned DEFAULT NULL,
  `textid` varchar(50) COLLATE latin1_general_cs DEFAULT NULL,
  `episode` tinyint(3) unsigned DEFAULT NULL,
  `enttype` tinyint(3) unsigned DEFAULT NULL,
  `adid` mediumint(8) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_clin_eventdate_medcode` (`patid`,`eventdate`,`medcode`),
  KEY `idx_clin_eventdate_adid` (`patid`,`eventdate`,`adid`)
) ENGINE=InnoDB AUTO_INCREMENT=62407536 DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs

"explain" returns the following:

*************************** 1. row ********************
           id: 1
  select_type: SIMPLE
        table: p
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 7
          ref: NULL
         rows: 182939
        Extra: Using index
*************************** 2. row ********************
           id: 1
  select_type: SIMPLE
        table: c1
         type: ref
possible_keys: idx_clin_eventdate_medcode,idx_clin_eventdate_adid
          key: idx_clin_eventdate_medcode
      key_len: 4
          ref: gprd_opadveff_extra_elisa.p.patid
         rows: 171
        Extra: Using where; Using index

Why is it not using the first 2 fields of idx_clin_eventdate_medcode, i.e. (patid,eventdate), but only patid (see ref column)?

If I change the where condition to equality, it works fine:

SELECT p.patid, MAX(c1.eventdate) as eventdate 
from patient as p 
left join op_adv_effects._clinical as c1 on p.patid = c1.patid 
where c1.eventdate = p.case_index 
group by p.patid

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 7
          ref: NULL
         rows: 182939
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: c1
         type: ref
possible_keys: idx_clin_eventdate_medcode,idx_clin_eventdate_adid
          key: idx_clin_eventdate_medcode
      key_len: 7
          ref: gprd_opadveff_extra_elisa.p.patid,gprd_opadveff_extra_elisa.p.cas
e_index
         rows: 1
        Extra: Using index

Thank you a lot in advance

How to get messages from MySQL database and print them in groups determined by Sender?

Say I have a table in MySQL database: id, msgto, msgfrom, message, date.

id = just message of it.
msgto = user ID who the message belongs to.
msgfrom = From whom the message is.
message = Message itself.
date = date of message.

So I want to make message system and display it as threads, so SELECT * FROM messages WHERE msgto=[current users ID]

Then I want to have messages grouped to sections/arrays like based on msgfrom variable.

So say there are 15 messages with msgfrom=5 and msgto=[current user] So basically there are 15 messages from user with ID of 5, how can I basically have them grouped and printed out in ID descending order (newest first)?

End result should be like:

message 4 from user1
message 3 from user1
message 2 from user1
message 1 from user1

[new group]
message 3 from user2
message 2 from user2
message 1 from user2

Printed in PHP by the way.

Multiple ORDER BY (get latest element) MySQL

I have the following tables : Radios, Podcasts and Shows. A radio has many podcasts, and a podcasts has many shows. Each podcast can be ordered by its history, and each show can be ordered by its publication_date.

I want to get all the podcasts associated with their latest show.

The query looks like that :

SELECT r.name AS radio_name,Pod.*,Sh.* 
FROM podcasts Pod 
  INNER JOIN radios r ON (r.id=Pod.radio_id) 
  INNER JOIN shows Sh ON (Sh.podcast_id=Pod.id) 
ORDER BY Pod.history LIMIT 5

I'd like to have a second ORDER BY Sh.publication_date but I don't really know where it should be.

MySQL: Query with join

I'm trying to use the longitude and latitude data in one of my tables to find the timezone for each entry in a table.

One of my tables, let's call it Table1 has the format below

latitude | longitude
12.12232 | 12.122322
23.23232 | 23.232323

I have been able to populate a table with each timezone and their latitude/longitude data. This table is called destination.

I now need to create and populate a column in Table1 to display the closest timezone for each row of data. The link here uses static SET @my_lat = 35.229205 ; -- the source latitude SET @my_lon = -114.26811 ;-- the source longitude data to calculate the closest timezone. I would like to do this using a dynamic query. My approach thus far has been

-- Add column to Table1 
ALTER TABLE Table1
MODIFY timezone_fixed VARCHAR(200);

-- Insert data into Table1's timezone_fixed column
INSERT INTO Table1 (timezone_fixed)
SELECT timezone FROM 
(SELECT *, ( 3959 * acos( cos( radians(
tbl.latitude) ) * cos( radians( 
destination.latitude ) ) * cos( radians( 
destination.longitude ) - radians(
tbl.longitude) ) + sin( radians(
tbl.latitude) ) * sin( radians( 
destination.latitude ) ) ) ) AS distance 
FROM destination ds
-- destination is a table that contains latitude, longitude and timezone for each timezone. This was populated in the previous step.
JOIN (SELECT longitude, latitude FROM Table1) tbl
ORDER BY distance LIMIT 1);

I have a feeling that this won't populate the closest timezone for each row in Table1 but the closest one to any of the rows in Table1. Also, I'm not sure if this is the most efficient way to find the closest timezone for each entry. Any help and tips would be appreciated.

wamp is not starting completely

I have latest version of wamp i.e WAMPSERVER (64 BITS & PHP 5.5) 2.5 installed. Its not starting completely, not turning to green and so mysql is not starting even though the port is used by apache itself.

I have also installed Visual Studio 2012 VC 11 vcredist_x64/86.exe for my Windows 8.1 64bit OS.

Very weird, all the paths are configured in paths and also extensions enabled. Please help!!

All fields in database updated at once using PHP

I have a form that comes from a link in a table that should just update one record in my database. When I changed some details in the table and pressed my submit button it changed all of my fields in the database and not just the one I wanted to change. Below is my form code and also the table that is being edited.

Edit user code

<?php

 // since this form is used multiple times in this file, I have made it a function that is easily reusable
 function renderForm($userID, $username, $password, $telephone, $address1, $town, $postcode, $forename, $surname, $email, $error)
 {
 ?>
 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://ift.tt/nYkKzf">
 <html>
 <head>
 <title>Edit User</title>
 </head>
 <body>
 <?php 
 // if there are any errors, display them
 if ($error != '')
 {
 echo '<div style="padding:4px; border:1px solid red; color:red;">'.$error.'</div>';
 }
 ?> 
 
 <form action="" method="post">
 <input type="hidden" name="userID" value="<?php echo $userID; ?>"/>
 <div>
 <p><strong>ID:</strong> <?php echo $userID; ?></p>
 <strong>Username: </strong> <input type="text" name="username" value="<?php echo $username; ?>"/><br/>
 <strong>Password: </strong> <input type="text" name="password" value="<?php echo $password; ?>"/><br/>
 <strong>Telephone: </strong> <input type="text" name="telephone" value="<?php echo $telephone; ?>"/><br/>
 <strong>Address: </strong> <input type="text" name="address1" value="<?php echo $address1; ?>"/><br/>
 <strong>Town: </strong> <input type="text" name="town" value="<?php echo $town; ?>"/><br/>
 <strong>Postcode: </strong> <input type="text" name="postcode" value="<?php echo $postcode; ?>"/><br/>
 <strong>Forename: </strong> <input type="text" name="forename" value="<?php echo $forename; ?>"/><br/>
 <strong>Surname: </strong> <input type="text" name="surname" value="<?php echo $surname; ?>"/><br/>
 <strong>Email: </strong> <input type="text" name="email" value="<?php echo $email; ?>"/><br/>

 <input type="submit" name="submit" value="Edit details">
 </div>
 </form> 
 </body>
 </html> 
 <?php
 }



 // connect to the database
 include "config.php";
 
 // check if the form has been submitted. If it has, process the form and save it to the database
 if (isset($_POST['submit']))
 { 
 // confirm that the 'id' value is a valid integer before getting the form data
 if (is_numeric($_POST['userID']))
 {
 // get form data, making sure it is valid
 $userID = $_POST['userID'];
 $username = $_POST['username'];
 $password = $_POST['password'];
 $telephone = $_POST['telephone'];
 $address1 = $_POST['address1'];
 $town = $_POST['town'];
 $postcode = $_POST['postcode'];
 $forename = $_POST['forename'];
 $surname = $_POST['surname'];
 $email = $_POST['email'];
 
 // check that firstname/lastname fields are both filled in
 if ($username == '' || $password == '' || $telephone == '' || $address1 == '' || $town == '' || $postcode == '' || $forename == '' || $surname == '' || $email == '' )
 {
 // generate error message
 $error = 'ERROR: Please fill in all required fields!';
 
 //error, display form
 renderForm($userID, $username, $password, $telephone, $address1, $town, $postcode, $forename, $surname, $email, $error);
 }
 else
 {
 // save the data to the database
        $query = $db->prepare("UPDATE user SET username='$username', password='$password', telephone='$telephone', address1='$address1', town='$town', postcode='$postcode', forename='$forename', surname='$surname', email='$email' ");
        $query->execute();
 
 // once saved, redirect back to the view page
 header("Location: view_user.php"); 
 }
 }
 else
 {
 // if the 'id' isn't valid, display an error
 echo 'Error!';
 }
 }
 else
 // if the form hasn't been submitted, get the data from the db and display the form
 {
 
 // get the 'id' value from the URL (if it exists), making sure that it is valid (checing that it is numeric/larger than 0)
 if (isset($_GET['userID']) && is_numeric($_GET['userID']) && $_GET['userID'] > 0)
 {
 // query db
 $userID = $_GET['userID'];
 $query = $db->prepare("SELECT * FROM user WHERE userID=$userID");
 $query->execute();
 $dbRow = $query->fetch(PDO::FETCH_ASSOC);
 
 // check that the 'id' matches up with a row in the databse
 if($dbRow)
 {
 
 // get data from db
 $username = $dbRow['username'];
 $password = $dbRow['password'];
 $telephone = $dbRow['telephone'];
 $address1 = $dbRow['address1'];
 $town = $dbRow['town'];
 $postcode = $dbRow['postcode'];
 $forename = $dbRow['forename'];
 $surname = $dbRow['surname'];
 $email = $dbRow['email'];
 
 
 // show form
 renderForm($userID, $username, $password, $telephone, $address1, $town, $postcode, $forename, $surname, $email, '');
 }
 else
 // if no match, display result
 {
 echo "No results!";
 }
 }
 else
 // if the 'id' in the URL isn't valid, or if there is no 'id' value, display an error
 {
 echo 'Error!';
 }
 }
?>

View user info code

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta name="description" content="">
    <meta name="author" content="">
    <link rel="icon" href="../../favicon.ico">

    <title>Ballymena Sports</title>

    <!-- Bootstrap core CSS -->
    <link href="bootstrap.min.css" rel="stylesheet">

    <!-- Custom styles for this template -->
    <link href="home2.css" rel="stylesheet">

    <!-- HTML5 shim and Respond.js for IE8 support of HTML5 elements and media queries -->
    <!--[if lt IE 9]>
      <script src="http://ift.tt/1xwklwE"></script>
      <script src="http://ift.tt/1qIredN"></script>
    <![endif]-->
  </head>

  <body>

    <nav class="navbar navbar-inverse navbar-fixed-top" role="navigation">
      <div class="container">
        <div class="navbar-header">
                                <a class="navbar-brand" href="home2_template.html">Ballymena Sports</a>
                </div>
                
                <ul class="nav navbar-nav navbar-right">
                    <li><a href="admin_login.php">Administrator</a></li>
            <li><a href="logout.php">Log out</a></li>
                </ul>
                
          </div>
    </nav>
        


    <!-- Main part of homepage -->
    <div class="jumbotron">
                <div class="container">
                  <h2>Users</h2>
                  <p>This table shows all registered users of Ballymena Sports:</p>            
                        
                        <div class="table-responsive"> 
                        <tbody>
                                <?php 
                                        include "config.php"; 
                                        
                                        $query = $db->prepare("SELECT * FROM user ORDER BY userID asc");
                                        $query->execute();
                
                
                                        echo "<table id='user' class='table table-bordered'>
                                                  <tr>
                                                  <th>User ID</th>
                                                  <th>Username</th>
                                                  <th>Forename</th>
                                                  <th>Surname</th>
                                                  <th>Email</th>
                                                  <th>Address</th>
                                                  <th>Town</th>
                                                  <th>Postcode</th>
                                                  <th>Edit User</th> 
                                                  <th>Delete User</th>
                                                  </tr>";
                                                
                                        while ($dbRow = $query->fetch(PDO::FETCH_ASSOC)) {
                                                $userID = $dbRow['userID'];
                                                $username = $dbRow['username'];
                                                $forename = $dbRow['forename'];
                                                $surname = $dbRow['surname'];
                                                $email = $dbRow['email'];
                                                $address1 = $dbRow['address1'];
                                                $town = $dbRow['town'];
                                                $postcode = $dbRow['postcode'];
                                                // code to display information
                                                
                                
                           { echo "<tr>
                                                <td>$userID</td>
                                                <td>$username</td>
                                                <td>$forename</td>
                                                <td>$surname</td>
                                                <td>$email</td>
                                                <td>$address1</td>
                                                <td>$town</td>
                                                <td>$postcode</td>
                                                <td><a href='edit_user.php?userID=".$userID."'>Edit</a></td>
                                                <td><a href='delete_user.php?userID=".$userID."'>Delete</a></td>
                                          </tr>";}
                                } //while
                                ?> 

                        </tbody>
                        </div>
                  </table>
                </div>
    </div>
<?php 


        if(!$_SESSION['admin_username']){
                header('location:admin_login.php'); 
                
                $name = $_SESSION['admin_username'];
        }
        
?> 

      <hr>



    <!-- Bootstrap core JavaScript
    ================================================== -->
    <!-- Placed at the end of the document so the pages load faster -->
    <script src="http://ift.tt/1qRgvOJ"></script>
    <script src="../../dist/js/bootstrap.min.js"></script>
    <!-- IE10 viewport hack for Surface/desktop Windows 8 bug -->
    <script src="../../assets/js/ie10-viewport-bug-workaround.js"></script> 
        <!-- Header and footer later to be used as include statements -->
  </body>
</html>