How to Insert Arrays (Multiple rows) into Mysql using Codeigniter?

i have search alot and i have post somany times but i didnt get any proper answer.

This is my view page - View

here i have write a query for add fields dynamically for particular ref_no so help me for model and controller

<div id="login_form">
        <?php echo form_open(base_url().'sample/invoice'); ?>
        <label for="type" class="control-label">Type</label>
        <div><?php echo form_input(array('id'=>'type','name'=>'type'));?></div>

        <label for="ref" class="control-label">REF</label>
        <div><?php echo form_input(array('id'=>'ref','name'=>'ref'));?></div>

        <label for="title" class="control-label">TITLE</label>
        <div><?php echo form_input(array('id'=>'title','name'=>'title'));?></div>

        <div  id="description"><p id="add_field">ADD DESCRIPTION</p></div>

        <label for="doc" class="control-label">Support Doc</label>
        <div><?php echo form_input(array('id'=>'doc','name'=>'attach','type'=>"file"));?></div>

        <input id="btn_add" name="btn_add" type="submit" class="btn btn-primary" value="Save" />
    </div>
<script>
            var count = 0;
    $(document).ready(function() {
        $('p#add_field').click(function(){
           count += 1;
            var html='<strong>Description  '+ count +'</strong>'+'<input id="description'+ count +'"name="description[]'+'" type="text" />'+'<input id="description'+ count +'"name="voucher_no[]'+'" type="text" />'+'<input id="description'+ count +'"name="price[]'+'" type="text" /><br />';
            $('#description').append(html);

    });
    });

        </script>

This is My Controller :

$data1 = array(
                  'invoice_type' => $this->input->post('type'),
                  'reference_no' => $this->input->post('ref'),
                  'des_title' => $this->input->post('title'),


                  );

    $data2 = array(
                    'reference_no' => $this->input->post('ref'),
                    'description' => $this->input->post('des'),
                    ); 
 $this->sample_model->insert_entry($data1, $data2);

This My MOdel :

function insert_entry($data1, $data2) {

        $this->db->insert('myinvoice', $data1);
        $this->db->insert('invoice_description', $data2);

}

What i want is insert mutiple descriptions for one reference number. myinvoice is parent table and invoice_description is child table when i insert single data its works perfectly but i want insert multiple descriptions

via Chebli Mohamed

How can I connect to a MySQL server on another computer?

Here I have some problem to connect to a MySQL server from another computer to my computer. I would like to use VB.net C# language to solve the problem.

Previously I used the following program to connect to a MySQL server from another computer to my computer, but this is not working anymore. Can anyone help me?

server = "192.168.8.124";
database = "Testing";
uid = "";
password = "1234";
string connectionString;
connectionString = "SERVER=" + server + "; PORT = 3306 ;" + "DATABASE=" + database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";
mycon = new MySqlConnection(connectionString);

via Chebli Mohamed

how to do bat file mysql

I have this code for how to do mysql bat file for backup, this is the code.

@echo off
echo Starting Backup of Mysql Database on server 
For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set dt=%%c-%%a-%%b)
For /f "tokens=1-4 delims=:." %%a in ('echo %time%') do (set tm=%%a%%b%%c%%d)
set bkupfilename=%1 %dt% %tm%.sql
echo Backing up to file: %bkupfilename%
mysqldump -p 3306 -h 192.168.0.1 -u root -p 123456 

bayanat>C:\mysql_daily_backups\"bayanat%bkupfilename%"

via Chebli Mohamed

cURL using info from mySQL, then storing the cURL'ed info

Maybe this is a simple thing - For me it's quite hard!

For starters, I'm new to programming in PHP (been doing it from time to time at the university when it was needed)and I've tried reading all sorts of guides and articles on the cURL subject. Those I've found usefull until now was mainly about how to CURL through one site with a lot of information, but what I really need is how to do it on multiple sites with not so much information - a few lines, as a matter of fact!

Another part is, the article focus is mainly at storing it at the FTP server in a txt file, but I have loaded around 900 addresses into mysql, and want to load them from there, and enrich the table with the information stored in the links - Which I will provided beneath!

We have some open public libraries with addresses and information about these and an API.

Link to the main site: http://dawa.aws.dk/

The function I would like to use: http://ift.tt/1IGVhWU SQL Structure: http://ift.tt/1KQfJeJ

fx this addresse: Dornen 2 6715 Esbjerg N in databasen.

http://ift.tt/1IGVhWW

This will give me the following output:

[
{
  "tekst": "Dornen 2, Tarp, 6715 Esbjerg N",
  "adresse": {
    "id": "0a3f50b8-d085-32b8-e044-0003ba298018",
    "href": "http://ift.tt/1KQfIaz",
    "vejnavn": "Dornen",
    "husnr": "2",
    "etage": null,
    "dør": null,
    "supplerendebynavn": "Tarp",
    "postnr": "6715",
    "postnrnavn": "Esbjerg N"
  }
}
]

For now, I just want to store it all in a blob, as seen in the SQL structure, but I'm a little off.. can anyone kick me in the right direction with a guide, an article - something that really helped you get by? :)

via Chebli Mohamed

How to remove duplicate rows from a mysql table

I have a table with a large number of columns. Say, the table has 1000 columns from 'col1' to 'col1000'.

For duplicate criteria I don't want to use 'col1' and 'col1000'. So, if in any row values for 'col2' to 'col999' have already occurred, they are duplicates.

I tried the solution given here http://ift.tt/1IGVjOs but this requires me to explicitly write all the columns I am considering for duplicates. I can't write 998 columns.

Can somebody please help me with the query in deleting such duplicates?

I want to delete such duplicates in a multiple tables.

via Chebli Mohamed

MySql How to get records as per request in the table mentioned

This is am employees record. Say some employees are manager to other employees and recognized by emp_id in manager_id column. For example Manager of Ram and Laxhman is Gopi, Manager of Krishna is Laxhman. Now i want to get records of emp_id, emp_name and his manager name. Below is the table

emp_id  emp_name    manager_id
1       gopi            0
2       Ram             1
3       Laxhman         1
4       krishna         3

Would anybody help me in sorting this query using mysql(in single query).

Many Thanks in advance.

via Chebli Mohamed

How to get all objects from table using Spring?

I try to get all objects back to the Java program(to the main(String[] args) method) from mysql database so I would be able to do some following actions. How can I do this in Spring? Can't find any information across the net. Thanks for help!:)

that's what I'm using

<dependencies>
    <dependency>
        <!-- jsoup HTML parser library @ http://jsoup.org/ -->
        <groupId>org.jsoup</groupId>
        <artifactId>jsoup</artifactId>
        <version>1.8.2</version>
    </dependency>
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>4.3.10.Final</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.36</version>
    </dependency>

    <dependency>
        <groupId>commons-dbcp</groupId>
        <artifactId>commons-dbcp</artifactId>
        <version>1.4</version>
    </dependency>
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-lang3</artifactId>
        <version>3.4</version>
    </dependency>
    <dependency>
        <groupId>javax.persistence</groupId>
        <artifactId>persistence-api</artifactId>
        <version>1.0.2</version>
    </dependency>

    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-entitymanager</artifactId>
        <version>4.3.10.Final</version>
    </dependency>

    <dependency>
        <groupId>javax.servlet</groupId>
        <artifactId>javax.servlet-api</artifactId>
        <version>3.0.1</version>
    </dependency>

    <!-- Spring -->

    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-core</artifactId>
        <version>4.1.7.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-beans</artifactId>
        <version>4.1.7.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-aop</artifactId>
        <version>4.1.7.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>4.1.7.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-orm</artifactId>
        <version>4.1.7.RELEASE</version>
    </dependency>


    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-web</artifactId>
        <version>4.1.7.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-webmvc</artifactId>
        <version>4.1.7.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-tx</artifactId>
        <version>4.1.7.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context-support</artifactId>
        <version>4.1.7.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-aspects</artifactId>
        <version>4.1.7.RELEASE</version>
    </dependency>

    <!-- Servlet and JSTL -->

    <dependency>
        <groupId>javax.servlet.jsp</groupId>
        <artifactId>jsp-api</artifactId>
        <version>2.1</version>
    </dependency>
    <dependency>
        <groupId>jstl</groupId>
        <artifactId>jstl</artifactId>
        <version>1.2</version>
    </dependency>

    <!-- Apache tiles -->

    <dependency>
        <groupId>org.apache.tiles</groupId>
        <artifactId>tiles-core</artifactId>
        <version>3.0.5</version>
    </dependency>
    <dependency>
        <groupId>org.apache.tiles</groupId>
        <artifactId>tiles-jsp</artifactId>
        <version>3.0.5</version>
    </dependency>
    <dependency>
        <groupId>org.apache.tiles</groupId>
        <artifactId>tiles-servlet</artifactId>
        <version>3.0.5</version>
    </dependency>

    <dependency>
        <groupId>org.apache.tiles</groupId>
        <artifactId>tiles-template</artifactId>
        <version>3.0.5</version>
    </dependency>
</dependencies>

via Chebli Mohamed

Flyway sql server on windows xp with windows authentification

I worked on Windows XP, with SQL Server 2008 r2(Express) installed and JDK 7 I've tried flyway to migrate to a database with Windows authentication but every time I run the migrate command this error message appears:

GRAVE: L'environnement d'exÚcution Java (JRE, Java Runtime Environment) version 1.7 n'est pas pris en charge par ce pilote. Utilisez la bibliothÞque de classes sqljdbc4.jar, qui permet la prise en charge de JDBC 4.0.

ERROR: java.lang.UnsupportedOperationException: L'environnement d'exÚcution Java (JRE, Java Runtime Environment) version 1.7 n'est pas pris en charge par ce pilote. Utilisez la bibliothèque de classes sqljdbc4.jar, qui permet la prise en charge de JDBC 4.0.

What should I do? I've already tried flyway with Oracle and MySQL and it works very well.

via Chebli Mohamed

unable to edit data in table using PHP form

Please look into this code unable to find out the actual error:

This is PHP upload code:

<?php
            include_once("config.php");
            if(isset($_GET['pro_id']))
            {
            $id=$_GET['pro_id'];
            if(isset($_POST['submitBtn']))          {
            $dept_id = $_POST['dept_id'];
            $cat_id = $_POST['cat_id'];
            /*$pro_id = $_POST['pro_id'];*/
            $pro_name = $_POST['pro_name'];
            $pro_desc = $_POST['pro_desc'];
            $pro_spec = $_POST['pro_spec'];
            $pro_price = $_POST['pro_price'];
            $status = 'on';
            $pro_keywords = $_POST['pro_keywords'];
            //image names
            $pro_image = $_FILES['pro_image']['name'];
            //temp images names
            $temp_image = $_FILES['pro_image']['tmp_name'];

            if($dept_id=='' OR $cat_id=='' OR $pro_name=='' OR $pro_desc=='' OR $pro_spec=='' OR $pro_price=='' OR  $pro_image=='' OR $pro_keywords=='')
                    {
            echo "<script>alert('All the fields are mandatory')</script>";
            exit();
                    }
            else
            {
            //upload image to folder
            move_uploaded_file($temp_image,"images/product_images/$pro_image");
            $run_query1 = mysqli_query($login, "update products1 SET (dept_id,cat_id,pro_name,pro_desc,pro_spec,pro_price,pro_image,status,date,pro_keywords) values (  '$dept_id','$cat_id','$pro_name','$pro_desc','$pro_spec','$pro_price','$pro_image','$status','NOW()','$pro_keywords' WHERE pro_id='$id'");

            if($run_query1)
                    {
                echo "<script>alert('Product updated successfully')</script>";
                exit();     
                    }
            else
                {
                echo "<script>alert('Errors')</script>";
                }
            }           }

            $query1 = mysqli_query($login, "select * from products1 where pro_id='$id'");
            $query2 = mysqli_fetch_array($query1);
            ?>

This the form Part where data retrieve from table and when click on the update button nothing happened and page is redirected to view data page and showing the old data:

<form action="ViewProduct.php" method="post" enctype="multipart/form-data" name="form1" id="form1">
            <table width="650" border="0">

            <tr>
                <td width="183" align="right">Department:</td>
                <th width="231" align="left">
                <select name="dept_id" id="dept_id">
                    <option>Select Department</option>
                 <?php
                $result=dept_show();

                while($row=mysqli_fetch_assoc($result))
                {
                    echo "<option value='{$row['dept_id']}'>{$row['dept_name']}</option>";
                }                    
                ?>
                </select></th></tr>
              <tr>
                <td width="183" align="right">Catagory</td>
                <th width="231" align="left">
                <select name="cat_id" id="cat_id">
                    <option>Select Catagory</option>
                <?php
                $result1=cat_show();

                while($row=mysqli_fetch_assoc($result1))
                {
                    echo "<option value='{$row['cat_id']}'>{$row['cat_name']}</option>";
                }                    
                ?>
                </select></th></tr>
              <tr>
                <!--<td width="231"><input type="hidden" name="pro_id" id="pro_id" value="<t?php echo $pro_id; ?>" /></td>-->
              </tr>
              <tr>
                <td align="right">Product Name/Model:</td>
                <td><input type="text" name="pro_name" id="pro_name" value="<?php echo $query2['pro_name']; ?>" /></td>
              </tr>
              <tr>
                <td align="right">Product Description:</td>
                <td><textarea type="textarea" name="pro_desc" id="pro_desc" cols="45" rows="5"><?php echo $query2['pro_desc']; ?></textarea></td>
              </tr>
              <tr>
                <td align="right">Products Specification:</td>
                <td><textarea type="textarea" name="pro_spec" id="pro_spec" cols="45" rows="5"><?php echo $query2['pro_spec']; ?></textarea></td>
              </tr>
              <tr>
                <td align="right">Product Price:</td>
                <td><input type="text" name="pro_price" id="pro_price" value="<?php echo $query2['pro_price']; ?>" /></td>
              </tr>
              <tr>
                <td align="right">Product Image:</td>
                <td><input type="file" name="pro_image" id="pro_image" value="<?php echo $query2['pro_image']; ?>" /></td>
                </tr>
                <tr>
                <td></td>
                <td><input size="45" type="text" name="text" id="text" value="<?php echo $query2['pro_image']; ?>" /></td>
              </tr>
              <tr>
                <td align="right">Keywords:</td>
                <td><input size="45" type="text" name="pro_keywords" id="pro_keywords" value="<?php echo $query2['pro_keywords']; ?>" /></td>
              </tr>
              <tr>
                <td colspan="2" align="center"><input type="submit" name="submitBtn" id="submit" value="Update" /></td>
              </tr>
            </table>
          </form>
            </div> <?php } ?>
        </td>
        </tr>
    </table>
  </div>

via Chebli Mohamed

How to connect to mysqld in windows from rails

Here i am trying to connect to mysql socket from rails in windows machine. but i am not sure that this is the right way to connect to mysql socket.

development:
  adapter: mysql2
  encoding: utf8
  database: walden
  pool: 5
  username: walden
  password: w@1d3n
  socket: /XAMPP/xamppfiles/var/mysql/mysql.sock

How can i connect to it.

via Chebli Mohamed

Send ID instead Name in PHP and MYSQL

i want to send ID to database table instead of Name. but i want to show Name in a field instead of ID. it work's well while i send it through combo box. but i don,t know how it work with Search field. the php code is given below:

<font> <b>Name: </b></font>
<?php

include("database/db.php");

if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}

$sql = "SELECT *  FROM table";

$result = $link->query($sql); ?>
<select name="ID">
<?php
if ($result->num_rows > 0) {
     // output data of each row
     while($row = $result->fetch_assoc()) {
         $ad_id = $row["ID"];
          $name= $row['Name'];
           ?>
       <option  value="<?php echo $ad_id; ?>"><?php echo  $name; ?></option>


<?php
     }
} else {
     echo "0 results";
}

// close connection
mysqli_close($link);
?> 
</select>

via Chebli Mohamed

jQuery autocomplete not working connected to mySQL database

I have an autocomplete form that was working completely fine. I leave my laptop and come back and now it doesn't work anymore.

My database is named wallettest

My table is named population and has 4 columns: Id, location, slug, Population.

I have 3 files: index.php, script.js and ajax_refresh.php.

Doing some testing, it seems it's not accessing my ajax_refresh.php which connects me to my database. So from there, I decided to put my ajax_refresh.php in my index.php file. Well, now I'm connected to my database but getting errors (I don't get why it wouldn't connect me in the first place, I had the right syntax in my file:

js file:"$.ajax({
            url: "ajax_refresh.php",

Anyways, file now looks like this and the error I get is below:

index.php:

<?php
// PDO connect *********
//CHECK TO SEE IF CONNECTION IS MADE
 $db = mysql_connect("localhost","root","butthead"); 
 if ($db) {
 echo("- Connection to database successful -");
 }
 else if (!$db) {
 die("Database connection failed miserably: " . mysql_error());
 }


function connect() {
    return new PDO('mysql:host=localhost;dbname=wallettest', 'root', 'butthead', array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
} 


$pdo = connect();
$keyword = '%'.$_POST['keyword'].'%';
$sql = "SELECT * FROM population WHERE slug LIKE (:keyword) ORDER BY population DESC LIMIT 0, 10";
$query = $pdo->prepare($sql);
$query->bindParam(':keyword', $keyword, PDO::PARAM_STR);
$query->execute();
$list = $query->fetchAll();
foreach ($list as $rs) {
    // put in bold the written text
    $slug = str_replace($_POST['keyword'], '<b>'.$_POST['keyword'].'</b>', $rs['slug']);
    // add new option
    echo '<li onclick="set_item(\''.str_replace("'", "\'", $rs['slug']).'\')">'.$slug.'</li>';
}

// END AJAX_REFRESH.PHP
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://ift.tt/kkyg93">
<html xmlns="http://ift.tt/lH0Osb">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Autocomplete using PHP/MySQL and jQuery</title>
<link rel="stylesheet" href="css/style.css" />
<script type="text/javascript" src="js/jquery.min.js"></script>
<script type="text/javascript" src="js/script.js"></script>
</head>


<body>
    <div class="container">
        <div class="header">

        </div><!-- header -->
        <h1 class="main_title">Autocomplete using PHP/MySQL and jQuery</h1>
        <div class="content">
            <form>
                <p>Table consists of : ID, Location, Slug, Population </p>
                <br><br>
                <div class="label_div">Search for a Slug : </div>
                <div class="input_container">
                    <input type="text" id="slug" onkeyup="autocomplet2()">
                    <ul id="list_id"></ul>
                </div>
            </form>
            <br><br><br><br>
            <p>List will be ordered from Highest population to lowest population (Top to bottom)</p>
                <br><br>
        </div><!-- content -->    
        <div class="footer">
            Powered by Jason's Fingers</a>
        </div><!-- footer -->
    </div><!-- container -->
</body>
</html>

script.js:

// autocomplete : this function will be executed every time we change the text
function autocomplet2() {
    var min_length = 3; // min caracters to display the autocomplete
    var keyword = $('#slug').val();
    if (keyword.length >= min_length) {
        $.ajax({
            url: 'ajax_refresh.php',
            type: 'POST',
            data: {keyword:keyword},
            success:function(data){
                $('#list_id').show();
                $('#list_id').html(data);
            }
        });
    } else {
        $('#list_id').hide();
    }
}

// set_item : this function will be executed when we select an item
function set_item(item) {
    // Changes input to the full name on selecting
    $('#slug').val(item);
    // Hides list after selection from list
    $('#list_id').hide();
}

function change()

Error:

via Chebli Mohamed

csv uploaded not working

i am uploading csv file.It is correctly upload in the folder. but data is not displaying when i given echo $row['adv_title']. this my controller.I want to display the title

public function upload{

 $config['upload_path'] = APPPATH.'/assets/upload/';
$config['allowed_types'] = 'csv';
$config['max_size']  = '5000';
$replace='"';

$this->load->library('upload', $config);
$this->load->database();

if ( ! $this->upload->do_upload('file_name'))
{
    $error=array('error' => $this->upload->display_errors());
    $this->session->set_flashdata('msg_excel','Choose a .csv file to upload'); 
    redirect(base_url().'admin/advertisement/adv');

}
else
{  
   $data=array('upload_data' => $this->upload->data());
  $userfile=$data['upload_data']['file_name'];
  $upload_data=$this->upload->data(); 
   $this->load->library('csvreader');
   $file=$upload_data['full_path'];
   $file_name=$upload_data['userfile']; 
    $data=$this->csvreader->parse_file($file);
    foreach($data as $row)
{
          echo "hiii".$row['adv_title']; 
}
}

what is the problem?

via Chebli Mohamed

How can i count the all the children & grand children until to the 7th level in PHP or mysql query

I have 3 tables right now

 1. members
 2. members_parents
 3. members_status

How can i count the children & grandchildren until to the depth level and separate the count() for the status 0 = free and 1 = subscriber

table: members - list all the records of members
table: members_parents - list of members & the parent member
table: members_status - list the members & parents status where 0 or 1

tbl_members_parents:
id     member     parent
1    10000      NULL
2    10001      10000
3    10002      10001

and so on.. how can i count the child & grandchildren until to the 7th level of grand children. thank you.

via Chebli Mohamed

How to submit dynamic form with multiple levels of tabs and modules in PHP?

I have a registration form of residential complex with multiple levels, as you can see. And I'm stuck.

Note: from the screenshot you can see that 1st level of tabs (houses) can have any number of tabs and so is 2nd one (porches).

Screenshot: http://ift.tt/1IknNk9

How to generate all that tricky staff, when onclick on tab, and mess with ids? I'm just really confused... How can I submit all that information into my MySQL DB from this form which generates dynamically?

Please, explain, show or code to help me in that complicated construction :c

Thank you!

via Chebli Mohamed

stored procedure to find the day from IN parameter date

please help me to find the passing IN parameter that give the day is Monday or else an weekday. give the working code for this stored procedure.

via Chebli Mohamed

how to store image in folder with unique name [on hold]

I am developing an app in android where user can post image and description in mysql using php script. I am successful with user login and registration also i am able to store the image inside folder in the mysql. But my problem is

how to store image in folder with unique name and store this path in mysql.

via Chebli Mohamed

SQL Upvote Downvote system

inexperienced SQL user here.

I am currently working on a forum where posts need to have an upvote/downvote system.

My current sql(phpmyadmin) structure is like this:

Table 1 (posts) post_id | post_title | post_score

Table 2 (pvotes) pvote_id | fk_post_id | fk_user_id | pvote_score

I want to somehow make post_score (in table 1), find all pvote_score(table 2) collumns and add/subtract them together, where fk_post_id(table 2) is = to post_id(table 1)

This way i hope to make a voting system that only allows every user to vote once, and automatically calculate a posts post_score from the pvote_score values.

EDIT:

Sorry if question wasnt clear enough. I want to know how i can make the post_score collumn automatically add/subtract the values from pvotes_score and show the sum as its value

Thank you for your attention, hope you can help :-)

via Chebli Mohamed

is it possible to connect mysql db using angular js without php code

Is it possible to connect MySql db using angularjs without PHP code? based on client side scripting we can't connect MySql db. Is it any other way to connection?

via Chebli Mohamed

Moving html data from mysql (wordpress) to sqlserver

I am upgrading a website to asp.net which have at least 100K posts in wordpress. I could not find any related topic for moving so i wanted to share my experience.

Big data is not a problem, however, some of wordpress tables have html data containing quotes (both single and double), &nbsp's, tab characters and so on. I have tried many ways for both exporting however, exporting to SQL file will not work for me (at least, i could not able to work with it, it causes so many troubles).

via Chebli Mohamed

Store Mysql CASE select statement result in a variable

I have the following MySQL trigger:

CREATE TRIGGER `upd_interim_final` AFTER INSERT ON `oee_main_interim`
 FOR EACH ROW INSERT INTO `oee_main_interim_final` (id,NAME,ts,Left_IO,Left_NIO,Recovery,Right_IO,Right_NIO,RunMode,S_TYPE,Shift,STD,curr_S_Type) 
VALUES(NULL, New.NAME, New.TS, NEW.Left_IO, New.Left_NIO,  New.Recovery, New.Right_IO, New.Right_NIO, New.RunMode, New.S_TYPE, 


 ( Select

  (Case
    When ((CurTime() > oee_machinenames.Shift1) And
    (CurTime() < oee_machinenames.Shift2)) Then 'Shift1'
    When ((CurTime() > oee_machinenames.Shift2) And
    (CurTime() < oee_machinenames.Shift3)) Then 'Shift2'
    When ((CurTime() > oee_machinenames.Shift3) Or
    (CurTime() < oee_machinenames.Shift1)) Then 'Shift3' End) As curr_Shift
From
  oee_machinenames
  where 
  oee_machinenames.ID = New.NAME

Group By
  oee_machinenames.ID),


  (Select
    `STD` From `oee_variant` Where `Machine_ID` = New.NAME And `S_TYPE` = 

(Select
  `S_TYPE`

From
  `v_getmaxid`
Where
  `NAME` = New.Name And
  v_getmaxid.Max_id In (Select
    Max(v_getmaxid.Max_id) As Max_Max_id
  From
    `v_getmaxid`
  Where
    `NAME` = New.Name))

     And `oee_variant`.`Operators` = 
    (Select `Operators` from `oee_machinenames` where `ID` = New.NAME)),


(Select
  `S_TYPE`

From
  `v_getmaxid`
Where
  `NAME` = New.Name And
  v_getmaxid.Max_id In (Select
    Max(v_getmaxid.Max_id) As Max_Max_id
  From
    `v_getmaxid`
  Where
    `NAME` = New.Name))

  )

I am trying to store the result of this CASE clause in a variable to use it later on in the trigger:

 ( Select

      (Case
        When ((CurTime() > oee_machinenames.Shift1) And
        (CurTime() < oee_machinenames.Shift2)) Then 'Shift1'
        When ((CurTime() > oee_machinenames.Shift2) And
        (CurTime() < oee_machinenames.Shift3)) Then 'Shift2'
        When ((CurTime() > oee_machinenames.Shift3) Or
        (CurTime() < oee_machinenames.Shift1)) Then 'Shift3' End) As curr_Shift
    From
      oee_machinenames
      where 
      oee_machinenames.ID = New.NAME

    Group By
      oee_machinenames.ID),

I have tried adding the following to the beginning of the trigger to no success:

DECLARE Shifts TEXT;
SET @Shifts := ( Select

      (Case
        When ((CurTime() > oee_machinenames.Shift1) And
        (CurTime() < oee_machinenames.Shift2)) Then 'Shift1'
        When ((CurTime() > oee_machinenames.Shift2) And
        (CurTime() < oee_machinenames.Shift3)) Then 'Shift2'
        When ((CurTime() > oee_machinenames.Shift3) Or
        (CurTime() < oee_machinenames.Shift1)) Then 'Shift3' End) As curr_Shift
    From
      oee_machinenames
      where 
      oee_machinenames.ID = New.NAME

    Group By
      oee_machinenames.ID);

Any help would be appreciated. Thanks

via Chebli Mohamed

After removing a table from the database, doctrine shows 'MappingException'. [Symfony]

Good morning, I tell you my case. I deleted a table and foreign keys of a database that I am using in a project under Symfony. After importing the mapping (XML) and generating entities, all automatically using Symfony console; when I access any page of the project, shows me the following exception that can not understand:

Fatal error:  Uncaught exception 'Doctrine\Common\Persistence\Mapping\MappingException' with message 'Class 'Consolidador\PanelBundle\Entity\Clients' does not exist' in C:\xampp\htdocs\integracion-v2\vendor\doctrine\common\lib\Doctrine\Common\Persistence\Mapping\MappingException.php:96
Stack trace:
#0 C:\xampp\htdocs\integracion-v2\vendor\doctrine\common\lib\Doctrine\Common\Persistence\Mapping\RuntimeReflectionService.php(41): Doctrine\Common\Persistence\Mapping\MappingException::nonExistingClass('Consolidador\\Pa...')
#1 C:\xampp\htdocs\integracion-v2\vendor\doctrine\common\lib\Doctrine\Common\Persistence\Mapping\AbstractClassMetadataFactory.php(281): Doctrine\Common\Persistence\Mapping\RuntimeReflectionService->;getParentClasses('Consolidador\\Pa...')
#2 C:\xampp\htdocs\integracion-v2\vendor\doctrine\common\lib\Doctrine\Common\Persistence\Mapping\AbstractClassMetadataFactory.php(311): Doctrine\Common\Persistence\Mapping\AbstractClassMetadataFactory->getParentClasses('Consolidador\\Pa...')
#3 C:\xampp\htdocs\integracion-v2 in C:\xampp\htdocs\integracion-v2\vendor\doctrine\common\lib\Doctrine\Common\Persistence\Mapping\MappingException.php on line 96

I checked out the database is generated correctly and that there is no longer any foreign key or table references in the database. Neither the xml mapping or entity have generated, so I do not understand why this exception.

I hope you can help me.

A greeting and thanks to all.

via Chebli Mohamed

Filtering a MySQL query with multiple conditions

I have my output array named "Feed Items" (fi):

(
    [0] => Array
        (
            [reg] => 2015-08-03 13:39:00
            [id] => fd7ec4107d16b07c1a13cbdd386af8d2cb05ffca
            [user_id] => de5fd44db1760b006b1909cf1db11a78b38e455c
            [img] => edee88e88cf6e17732e393b5433cfd894662902e
            [type] => new_join_ambition
        )

)

I generate an array named "RemovedFeedItems" (rfi) that has the following structure:

(
    [0] => Array
        (
            [object_id] => fd7ec4107d16b07c1a13cbdd386af8d2cb05ffca
            [postee_id] => de5fd44db1760b006b1909cf1db11a78b38e455c
            [type] => new_join_ambition
        )

)

My mission is to not get the records that have the following condition:

ri.id == rfi.object_id && ri.user_id == rfi.postee_id && ri.type == rfi.type

The query that I use to get the Feed Items array is:

SELECT 
                i.registered AS reg, 
                a.id, 
                u.id AS user_id, 
                ui.image_id AS img,  
                'new_join_ambition' AS type, 
            FROM x_ambition_invites i 
                LEFT JOIN x_user u
                    ON i.to = u.id
                LEFT JOIN x_user_images ui 
                    ON u.id = ui.user_id
                LEFT JOIN x_ambitions a
                    ON i.ambition_id = a.id
            WHERE a.registered 
                BETWEEN '2014-07-21 14:25:03' AND '2015-08-05 12:04:41' 
            AND i.to != '8fa7a1679560876eaf2f8060abd916b692c719dc' 
            AND i.to IN ('de5fd44db1760b006b1909cf1db11a78b38e455c')

How can I adapt my query to implement the condition to remove the stated record from the FeedItems Array?

Thanks in advance.

via Chebli Mohamed

System for Permission / Access Control Tracking (PHP+MYSQL based)

Have searched the web extensively, but no luck in this domain. Basically I have a simple requirement, currently when a user at our company requires System Access / E-Mail / Internet Access / Etc. they have to fill out a form, get the relevant authorized signatories and then send it to the IT Department for processing, in which we then mark who has completed that job and file it. This is a very old-school and manual system in my opinion and im looking for a "Paperless method" based on PHP and MYSQL, hosting this on a seperate web server to address this concern. So a few aspects I thought about this;

• Need to develop a form to capture the user's requirements (by navigating to an url)
• After the user submits, we (IT Department) need to assign an senior staff member of that department to authorize the permissions / request online.
• Thats IT! Of course we will need audit trails to track who did what and when, as well as a place to search for a user based on their name or at least their employee number (which will make mandatory in the form), a way to backup and restore the database and a section to create users as well as authorized senior management who will be doing the approvals.
• If we can get a system to upload the scanned form (as a picture) additionally, to be saved in the db as well, that would be an added advantage as well as being able to configure an SMTP server to enable notifications.
• Prefer (but not mandatory) if the web app has security in mind, so that its not vulnerable to XSS, SQL Injection, etc.

I'm surprised that with all the talented developers we have out there, no one has come up with an open source (php and mysql based) system readily available, so thought we could develop such a system as it would prove to be very useful to almost all companies big and small. I have looked into role-based access control and Identity Access Management but both those are not applicable, too advanced or over-kill for the task at hand.

Anyone familiar with a solution or can develop one, please let me know so that the whole community can benefit from this.

via Chebli Mohamed

How to check specific table is locked in Mysql?

I am executing below query to find the whether my temp table is locked.

show open tables where in_use > 0 and table = 'temp'

But it throwing below error :

Syntax error near 'table = 'temp'

I have searched but unable to get the correct syntax. Can anybody help.

via Chebli Mohamed

XML to MySQL when xml file has multiple matchin fields

I've been doing some work on an XML to MYSQL using loadXML. I have been successful with these in the past. The difference with the latest effort is that we have multiple occurunces of a fieldname in the MySQL. A sample of this is below:

<row>
<pictures>
        <picture name="Photo 1">
          <filename>image1.jpg</filename>
        </picture>
        <picture name="Photo 2">
          <filename>image2.jpg</filename>
        </picture>
        <picture name="Photo 4">
          <filename>image3.jpg</filename>
        </picture>
        <picture name="Photo 3">
          <filename>image4.jpg</filename>
        </picture>
        <picture name="Photo 7">
          <filename>image5.jpg</filename>
        </picture>
        <picture name="Photo 6">
          <filename>image6.jpg</filename>
        </picture>
        <picture name="Photo 5">
          <filename>image7.jpg</filename>
        </picture>
        <picture name="Photo 8">
          <filename>image8.jpg</filename>
        </picture>
        <picture name="Photo 9">
          <filename>image9.jpg</filename>
        </picture>
      </pictures>
</row>

I need to import this into a MySQL table with the fields:

picture1 picture2 picture3 picture4 picture5 picture6 picture7 picture8 picture9

As you can see, the 'name' attribute doesn't necessarily occur in the correct order, so I need them to simply be inserted in order. So the first to go to picture1, the second to picture2 etc..

What is currently being achieved is that I always end up with the last entry in the list being in the table. This is I assume because the filed is being overwritten each time.

Any ideas how to achieve this? I have found similar queries to this but no answers as yet and have been looking for a good while. The rest of the file is loading fine as they have unique fieldnames and can easily be mapped to a MySQL column, but I am struggling with this one.

Any help appreciated.

via Chebli Mohamed

How to select a data from a table using the where and like statement and echo it

I am having a issue on how to select a data from a database table using the were and like statement.

$hy=mysql_query("select (Total) AS firstterm FROM studentmark, subject where studentmark.student_id='$name' AND studentmark.YEAR='$ya' AND subject.code=studentmark.code AND studentmark.TERM='$term' LIKE 'F%'"); $hm=mysql_num_rows($hy); $fetch=mysql_fetch_array($hy);

echo $fetch['firstterm'];

the issue is that the LIKE 'F%' (FIRST) in the term which has 89 as Total was not selected in the table but the LIKE 'S%' (SECOND) in the term which has 73 was selected. is there anything i am missing?

the table below TERM | CODE |student_id|contAss20Asg|ClassWk10 |Test2nd10|YEAR |EXAM| TOTAL FIRST | AGR | John | 18 |5 | 7 |2011 | 59 | 89 SECOND |AGR2 |John | 13 |6 | 4 |2011 | 40 | 73 THIRD |AGR3 |John | 18 |6 | 8 |2011 | 34 | 64 FIRST |BIO |John | 12 |3 | 3 |2011 | 55 | 73 SECOND |BIO2 |John | 14 |8 | 7 |2011 | 56 | 85 THIRD |BIO3 |John | 12 |8 | 8 |2011 | 42 | 70

My code is stated below

<?php echo '</td><td>'?>
  <?php 
    if ($fetch['Total']==NULL){
echo 'missed';
}else 
    $hy=mysql_query("select  (Total) AS secondterm FROM studentmark, subject where studentmark.student_id='$name' AND studentmark.YEAR='$ya' AND subject.code=studentmark.code    AND studentmark.TERM='$term' LIKE 'S%'");
$hm=mysql_num_rows($hy);
$fetch=mysql_fetch_array($hy);
echo $fetch['secondterm'];
?>
<?php echo '</td><td>'?>
  <?php 
    if ($fetch['Total']==NULL){

}else 
    $hy=mysql_query("select  (Total) AS firstterm FROM studentmark, subject where studentmark.student_id='$name' AND studentmark.YEAR='$ya' AND subject.code=studentmark.code    AND studentmark.TERM='$term' LIKE 'F%'");
$hm=mysql_num_rows($hx);

$hm=mysql_num_rows($hy);
$row=mysql_fetch_array($hy);
echo $row['secondterm'];


?>

<?php echo '</td><td>'?>
  <?php 
    if ($fetch['Total']==NULL){
//echo 'missed';
}else 
    $hy=mysql_query("select  (Total) AS thirdterm FROM studentmark, subject where studentmark.student_id='$name' AND studentmark.YEAR='$ya' AND subject.code=studentmark.code    AND studentmark.TERM='$term'");
$hm=mysql_num_rows($hy);

$hm=mysql_num_rows($hy);
$fetch=mysql_fetch_array($hy);
$row=mysql_fetch_array($hy);

echo $fetch['firstterm']+ $row['secondterm'] + $fetch['thirdterm'];

?>

via Chebli Mohamed

not able to load data from database in dhtmlxgantt chart

i have followed all steps for making dhtmlxgantt chart but still i am not able to display data from my database . my output from conncection data.php to mysql database is { "data":[{"id":"2","start_date":"2013-04-05 00:00:00","duration":"11","text":"Project #1","progress":"0.6","sortorder":"1","parent":"0"},{"id":"1","start_date":"2013-04-10 00:00:00","duration":"11","text":"Project #1","progress":"0.6","sortorder":"1","parent":"1"}], "collections": {"links":[{"id":"1","source":"1","target":"2","type":"1"},{"id":"2","source":"2","target":"1","type":"1"},{"id":"3","source":"2","target":"1","type":"1"}]}}

and my gantt.html code is

<!DOCTYPE html>
<style type="text/css" media="screen">
    html, body{
        margin:10px;
        padding:0px;
        height:100%;
        overflow:hidden;
    }   
</style>
<head>
   <title>How to Start with dhtmlxGantt</title>
   <script src="codebase/dhtmlxgantt.js"></script>   
   <link href="codebase/dhtmlxgantt.css" rel="stylesheet">   
</head>
<body>
    <div id="gantt_here" style='width:1000px; height:400px;'></div>
    <script type="text/javascript">
     var tasks = {
    data:[
        {id:1, text:"Test Project 1",start_date:"01-04-2013", duration:35,
        progress: 0.1, open: true},
        {id:2, text:"Wire Framing",   start_date:"03-04-2013", duration:5, 
        progress: 0,   open: true, parent:1},
        {id:3, text:"Design",   start_date:"10-04-2013", duration:20, 
        progress: 0.0, open: true, parent:1},
        {id:4, text:"Database Design", start_date:"10-04-2013", duration:5, 
        progress: 0,   open: true, parent:1},
        {id:5, text:"System Set up", start_date:"11-04-2013", duration:2, 
        progress: 0.0, open: true, parent:1},
        {id:6, text:"System Presentation", start_date:"24-04-2013", duration:1, 
        progress: 0.0, open: true, parent:1},
    {id:7, text:"Testing", start_date:"25-04-2013", duration:5, 
        progress: 0.0, open: true, parent:1},
    {id:8, text:"Bug Fixing", start_date:"30-04-2013", duration:5, 
        progress: 0.0, open: true, parent:1},
    {id:9, text:"Beta Launch", start_date:"05-05-2013", duration:1, 
        progress: 0.0, open: true, parent:1}
    ],
    links:[
        {id:1, source:1, target:2, type:"1"},
        {id:2, source:1, target:3, type:"1"},
        {id:3, source:1, target:4, type:"1"},
        {id:4, source:1, target:5, type:"1"},
        {id:5, source:1, target:6, type:"1"},
    {id:6, source:1, target:7, type:"1"},
    {id:7, source:1, target:8, type:"1"},
    {id:8, source:1, target:9, type:"1"}
    ]
};   
//  gantt.config.xml_date = "%Y-%m-%d %H:%i"; 

    gantt.init("gantt_here");   
    gantt.parse(tasks);
            gantt.load("data.php");
//  var dp=new dataProcessor("data.php");   
//  dp.init(gantt);

    </script>

    </body>
</html>

and also my data.php code is

<?php
 
include ('codebase/connector/gantt_connector.php');
 
$res=mysql_connect("localhost","root","");
mysql_select_db("gantt");
 
$gantt = new JSONGanttConnector($res);
$gantt->render_links("gantt_links","id","source,target,type");
$gantt->render_table(
    "gantt_tasks",
    "id",
    "start_date,duration,text,progress,sortorder,parent"
);
?>

so please if anyone could tell me why the data from database is not displaying it would be very helpfull. thanks in advance

via Chebli Mohamed

Calling PHP Function through JavaScript with Parameter

I have a function in a separate PHP page to add information to a MySql database. However if I want to pass in a paramater which is only available in my JavaScript file.

PHP Function in ModelBrowse.php

function databaseAdd($lockval)
{
    include_once('../dbConnect.php'); 

    $con = connect();
        if (!$con) {
            trigger_error(mysqli_error($con), E_USER_ERROR);
            die('Could not connect: ' . mysqli_error($con));
        }
        if ($lockval = "locked")
        {
        $sql = "INSERT INTO model(lock) VALUES ('lock')";
        }

        if ($lockval = "unlocked")
        {
        $sql = "INSERT INTO model(lock) VALUES ('unlock')";
        }


        mysqli_query($con, $sql)

        mysqli_close($con);
}

I want to call databaseAdd in my JavaScript file. I know I must use AJAX but I am confused on how I must do this.

via Chebli Mohamed

Unable to connect to database using jsp in ubuntu

I am trying to connect with the mysql database in linux. I know I am making mistake in including the jar file. I don't know how to give reference to those jar files in linux the way we reference them in eclipse or Netbeans. I am running those jsp pages using tomat7.

type Exception report

message An exception occurred processing JSP page /first.jsp at line 9

description The server encountered an internal error that prevented it from fulfilling this request.

exception

org.apache.jasper.JasperException: An exception occurred processing JSP page /first.jsp at line 9

6: <% 7:
8: out.println("Hello World!"); 9: Class.forName("com.mysql.jdbc.Driver"); 10: Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/MeshliumDB","myuser","mypass"); 11: if(con!=null) 12: out.println("Connection Established");

Stacktrace: org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:568) org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:455) org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390) org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334) javax.servlet.http.HttpServlet.service(HttpServlet.java:727)

root cause

javax.servlet.ServletException: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:916) org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:845) org.apache.jsp.first_jsp._jspService(first_jsp.java:86) org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70) javax.servlet.http.HttpServlet.service(HttpServlet.java:727) org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432) org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390) org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334) javax.servlet.http.HttpServlet.service(HttpServlet.java:727)

root cause

java.lang.ClassNotFoundException: com.mysql.jdbc.Driver org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1718) org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1569) org.apache.jasper.servlet.JasperLoader.loadClass(JasperLoader.java:126) org.apache.jasper.servlet.JasperLoader.loadClass(JasperLoader.java:63) java.lang.Class.forName0(Native Method) java.lang.Class.forName(Class.java:191) org.apache.jsp.first_jsp._jspService(first_jsp.java:73) org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70) javax.servlet.http.HttpServlet.service(HttpServlet.java:727) org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432) org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390) org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334) javax.servlet.http.HttpServlet.service(HttpServlet.java:727)

via Chebli Mohamed

Is it possible to isolate db table from read operations in transaction?

I'm writing Java app(Spring Data/Hibernate/JPA), which should work with MySQL DB. I have a simple task, which should be atomic: 1. Retrieve first item from database 2. Bind this item to current user(UPDATE item SET user_id=current_user_id WHERE id=id)

I've put 500ms pause between these two steps to test it against concurrency and run it in transaction. Transaction exists, at least it rollbacks if I throw exception in this code. But atomicity doesn't work as I want it to, with this 500ms pause I get the following situation:

1. user1 starts transaction
2. user1 retrieves first item - item1
3. user2 starts transaction
4. user2 retrieves first item - item1
5. user1 updates item1
6. user1 commits transaction
7. user2 updates item1
8. user2 commits transaction

Is there a way to isolate table from user2's read/write queries while user1 is in transaction? It should be DB level transaction to allow 2 instances of the app on different servers.

via Chebli Mohamed

Create Virtual Column in mysql

I have mysql query like this:

$statement=$pdo->prepare("SELECT id AS Activity_ID,type,status,description,
(SELECT SUM(price*worked_hours) FROM tbl_working_hours 
WHERE id_act = Activity_ID) AS total_hours FROM tbl_activity");

There is two tables:

1- tbl_activity

2- tbl_working_hours

I am trying to create virtual column for each row based on summary from another table.

Thanks

via Chebli Mohamed

HOW to echo New record with selected old record in PHP Mysql?

I am new in PhP. I have a query

"SELECT r.client_id,c.id,t.id,a.id,o.id,c.name as cname,t.title as ttitle,a.title as atitle,o.title as otitle, l.title as ltitle, s.title as stitle
FROM og_ratings r 
LEFT JOIN og_companies c
ON r.client_id = c.id
LEFT JOIN og_rating_types t
ON r.rating_type_id = t.id
LEFT JOIN og_actions a
ON r.pacra_action = a.id
LEFT JOIN og_outlooks o
ON r.pacra_outlook = o.id
LEFT JOIN og_lterms l
ON r.pacra_lterm = l.id
LEFT JOIN og_sterms s
ON r.pacra_sterm = s.id
WHERE c.id= 338
ORDER BY r.id DESC
LIMIT 2";

Result of My query is

Now i want to print first row of of resulted query and i success. But now i want to echo only two columns ltitle and Stitle from second row. Here i failed.

Here is my code

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "pacra1";


$conn = new mysqli($servername, $username, $password, $dbname);
//$id2 = $_GET['id'];
$sql= "SELECT r.client_id,c.id,t.id,a.id,o.id,c.name as cname,t.title as ttitle,a.title as atitle,o.title as otitle, l.title as ltitle, s.title as stitle
FROM og_ratings r 
LEFT JOIN og_companies c
ON r.client_id = c.id
LEFT JOIN og_rating_types t
ON r.rating_type_id = t.id
LEFT JOIN og_actions a
ON r.pacra_action = a.id
LEFT JOIN og_outlooks o
ON r.pacra_outlook = o.id
LEFT JOIN og_lterms l
ON r.pacra_lterm = l.id
LEFT JOIN og_sterms s
ON r.pacra_sterm = s.id
WHERE c.id= 338
ORDER BY r.id DESC
LIMIT 1";
$result = $conn->query($sql);
//$array = array('1','2','3');

while ($row = $result->fetch_assoc()){

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://ift.tt/kkyg93">
<html xmlns="http://ift.tt/lH0Osb">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>

<table border="1">
        <tr>
            <td> ID </td>
            <td> <?php echo $row['client_id'] ?> </td>
        </tr>

        <tr> 
            <td>Name </td>
            <td><?php echo $row['cname'] ?> </td>
        </tr>

        <tr>
            <td>Rating Type </td>
            <td><?php echo $row['ttitle'] ?> </td>
        </tr>
        <tr>
            <td>Action </td>
            <td><?php echo $row['atitle'] ?> </td>
        </tr>
        <tr>
            <td>Outlook </td>
            <td><?php echo $row['otitle'] ?></td>
        </tr>
        <tr>
            <td rowspan="2">Long Term Rating </td>
            <td>Current (<?php echo $row['ltitle'] ?>) <tr><td>Previous (<?php echo $row['ltitle'][0] ?>)</td> </tr></td>
        </tr>

        <tr>
            <td rowspan="2">Short Term Rating </td>
            <td>Current (<?php echo $row['stitle'] ?>) <tr><td>Previous (<?php echo $row['stitle'][0] ?>)</td> </tr></td>
        </tr>


</table>

</body>
</html>

<?php
}?>

Result of my code is

In Previos column of my code i want to print second row data of my db table. You can see my result is wrong. Can you guys please help me?

via Chebli Mohamed

How To Create Query With Conditional Group By

I have table like this :

+---------+---------------+
| item_id | status        |
+---------+---------------+
| 1       | active        |
| 1       | sold          |
| 1       | deleted       |
| 2       | active        |
| 2       | sold          |
| 3       | active        |
+---------+---------------+

what I want is simple output like this. But, with one condition. if some item_id already have 'deleted' status, then it wont show again in this query. and all item_id are grouped into single line.

+---------+---------------+
| item_id | status        |
+---------+---------------+
| 2       | active        |
| 3       | active        |
+---------+---------------+

I tried using : SELECT * FROM table GROUP BY item_id, but the result is not as I expected.

via Chebli Mohamed

Executing a Query to Insert Data from VBA to MySQL and What Will Said Data Look Like

I'm trying to insert snippets of a word document into a MySQL database. I did this in Java, but I lost all formatting of the Microsoft document, so I'm now trying to do the same in VBA (I'm hoping that this will keep the formatting?!). But to find out if it will, I need to try.. I've set up the connection and the table in MySQL but I can't insert any values. It throws up a syntax error on the "conn.Execute" line.

Sub ConnectToDataBase()

'
'
 Dim conn As New ADODB.Connection
 Dim Server_Name As String
 Dim Database_Name As String
 Dim User_ID As String
 Dim Password As String
 '
 Dim i As Long ' counter
 Dim SQLStr As String ' SQL to perform various actions
 Dim table1 As String, table2 As String
 Dim field1 As String, field2 As String
 Dim rs As ADODB.Recordset
 Dim vtype As Variant
 '


 Server_Name = "127.0.0.1" ' Enter your server name here -
 Database_Name = "rmp" ' Enter your database name here
 User_ID = "root" ' enter your user ID here
 Password = "Password1" ' Enter your password here

 Set conn = New ADODB.Connection
conn.Open "DRIVER={MySQL ODBC 3.51 Driver}" _
 & ";SERVER=" & Server_Name _
 & ";DATABASE=" & Database_Name _
 & ";UID=" & User_ID _
 & ";PWD=" & Password _
 & ";OPTION=16427" ' Option 16427 = Convert LongLong to Int: 

 strSQL = "INSERT INTO  parts(idParts, Part 1, Part 2, Part 3, Part 4, 
 Part 5) " & _
            "VALUES (' " & 2 & " ' , '" & one & "' , '" & two & "' , '" &  
  three & "' , '" & four & "' , '" & five & "' )"

conn.Execute strSQL


Close connections
On Error Resume Next
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
On Error GoTo 0

End Sub

via Chebli Mohamed

how to unset query variable

In order to fetch data from database (mySql) i am appending my query depending upon user requirement which he chooses from the filters i've given to him. but at a specific point if certain condition is met, so i want to unset a field (submissionDate) which was set to some variable before and set some new field to that variable. For e.g my query is `

$query = "SELECT * FROM table2 WHERE DATE(submissionDate) between '$variablename3' and '$variablename4'"

but if following condition is true

if($variablename8=="Approved")

so i have to Append previous query and add to it

$query = "$query and DATE(newDate) between '$variablename3' and '$variablename4'"

`Note that i am appending it depending upon conditions met. so can't write all query again . Different fields will be set to different values but only if this condition is met i have to unset submissionDate and set newDate field b/w var3 and var4 and unset previous part which was

DATE(submissionDate) between '$variablename3' and '$variablename4'

So basically i have to neglect submissionDate effect while it will remain in the query. is there any possible way to sort this type of problem out ?

via Chebli Mohamed

Errors connecting to MySQL database from alternate domain

I have two domains on two separate servers, foo.com and bar.com.

I have a website and MySQL database setup on foo that I want to migrate to bar, but bar doesn't have MySQL.

As a solution, I'm moving all the files across but leaving the database on foo, and connecting to it remotely.

I'm currently connecting like so:

connect.php

$hostname = 'database.foo.com';
$username = 'username';
$password = 'password';
$dbname   = 'database';

try {
    $dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
}

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

This works fine on foo.com but when I migrate this file to bar.com I get this error:

SQLSTATE[HY000] [2003] Can't connect to MySQL server on 'database.foo.com' (110)

I can have the two sites, foo.com and bar.com open in two windows, and one will work while the other doesn't - despite the fact that they are both connecting (or trying to connect) to the same database.

Why is this happening and how can this be rectified?

via Chebli Mohamed

converting a php mysql to json for multilevel menu Where Branches are child of perticular node

[{ "id":"100", "label":"Main Incomer", "inode":true, "checkbox":true, "radio":false, "branch":[ {"id":"101", "label":"LT-1", "inode":true, "checkbox":true, "radio":false },

{"id":"102", "label":"LT-2", "inode":true, "checkbox":true, "radio":false,
"branch":[ {"id":"1", "label":"Costa Cofee", "inode":false, "checkbox":true, "radio":false },
{ "id":"2", "label":"Food Court", "inode":false, "checkbox":true, "radio":false },
{ "id":"3", "label":"MDB 2.1(Light & Power B 1,2,3)", "inode":false, "checkbox":true, "radio":false },
{ "id":"4", "label":"MDB 2.2(Air Washer & exhauste)", "inode":false, "checkbox":true, "radio":false },
{ "id":"5", "label":"MDB LG-1(Main Kitchen)", "inode":false, "checkbox":true, "radio":false },
{ "id":"6", "label":"MDB 2.1(AHU & TFA)", "inode":false, "checkbox":true, "radio":false },
{ "id":"7", "label":"MDB 4.3(AHU Exhaust & Scrubber)", "inode":false, "checkbox":true, "radio":false }]},

    {"id":"104", "label":"LT-3", "inode":true, "checkbox":true, "radio":false }

] }]';

via Chebli Mohamed

crystal report odbc login failed

My database is a remote Mysql database, I'm able to access the crystal reports of my application through various PC's but there is a problem with one PC, Even though I installed mysql connector and created a connection with my database which was successful but still when I try to open the report it prompts for the username and password! How can I resolve this issue?

via Chebli Mohamed

Many to many relation with ON DELETE CASCADE with Symfony and Doctrine

I want a simple many to many relation with Symfony and Doctrine. This is really a unidirectional one-to-many association can be mapped through a join table as the docs indicate I am using a YAML file for configure this with the following code:

In file Content.orm.yml:

manyToMany:
  comments:
    cascade: ["persist","remove"]
    onDelete: CASCADE
    options:
      cascade:
        remove: true
        persist: true
        #refresh: true
        #merge: true
        #detach: true
    orphanRemoval: false
    orderBy: null
    targetEntity: Comment
    joinTable:
      name: content_comments
      joinColumns:
        content_id:
          referencedColumnName: id
      inverseJoinColumns:
        comment_id:
          referencedColumnName: id
          unique: true

This produce the following SQL commands:

$ php app/console doctrine:schema:update --dump-sql | grep -i "comment\|content"
CREATE TABLE comment (id INT AUTO_INCREMENT NOT NULL, text LONGTEXT NOT NULL, content_id INT NOT NULL, creation_date DATETIME NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE contents (id INT AUTO_INCREMENT NOT NULL, user INT DEFAULT NULL, user_id INT NOT NULL,file VARCHAR(255) DEFAULT NULL, INDEX IDX_B4FA11778D93D649 (user), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE content_comments (content_id INT NOT NULL, comment_id INT NOT NULL, INDEX IDX_D297CC584A0A3ED (content_id), UNIQUE INDEX UNIQ_D297CC5F8697D13 (comment_id), PRIMARY KEY(content_id, comment_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
ALTER TABLE contents ADD CONSTRAINT FK_B4FA11778D93D649 FOREIGN KEY (user) REFERENCES users (id);
ALTER TABLE content_comments ADD CONSTRAINT FK_D297CC584A0A3ED FOREIGN KEY (content_id) REFERENCES contents (id);
ALTER TABLE content_comments ADD CONSTRAINT FK_D297CC5F8697D13 FOREIGN KEY (comment_id) REFERENCES comment (id);

But as you can see, the FOREIGN KEY instructions doesn't have the parte "ON DELETE CASCADE", even I try to put all the YAML annotations that I found.

Because in code, I am trying to delete a "content" entity and all the "comments" associated with this code:

        $comments = $content->getComments();

        // Remove first the parent
        $entity_manager->remove($content);
        $entity_manager->flush();

        // Remove the childs
        foreach($comments as $comment)
        {
            $entity_manager->remove($comment);
        }

        $entity_manager->flush();

This produce the following exception.

An exception occurred while executing 'DELETE FROM comment WHERE id = ?' with params [1]:\n\nSQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`bb2server`.`content_comments`, CONSTRAINT `FK_D297CC5F8697D13` FOREIGN KEY (`comment_id`) REFERENCES `comment` (`id`))

So, what I am doing wrong? Or how to force to Doctrine to put "ON DELETE CASCADE" in many to many relations?

via Chebli Mohamed

Insert Data from Table A to Table B WHERE (conditions) & SET Col.a = "xxx"

| Table [A]                | Table [B]   
| id    value   data       | id    value   data
| ab     15      100       | ac     19      200
| ab     18      101       | ac     28      310
| ab     22      199       | ac     39      401

Table A is my old database table with history data. Table B is my current database table with corrected value in id

I'll like to insert historic data from Table A into Table B WHERE value<=xxxx AND value >=xxxx and also at the same time set the data from tablea.id = ac when inserted into Table B so the data outcome will be.

| Table [A]                | Table [B]   
| id    value   data       | id    value   data
| ab     15      100       | ac     19      200
| ab     18      101       | ac     28      310
| ab     22      199       | ac     39      401
|                          | ac     15      100
|                          | ac     28      101
|                          | ac     22      199

I'll be using INSERT IGNORE as my table has 3 column as it's composite primary key. Table description is just a sample. My table has alot more columns just highlighting the main difference for the data import I want to accomplish.

via Chebli Mohamed

How to update the cache value when database has change value of same data which is stored in cache

If i am using laravel 4.2 caching mechanism like bellow.

$users = DB::table('users')->remember(10)->get();

As i understand cache mechanism query execute one's and story it's value to cache and return data from cache upto 10 minutes.

But my problem is one's query will be executed and data stored it's cache inbetween user table updates it's value then how may i check and update cache value so i can get updated data.

Any one have idea any suggestion please let me know...?

via Chebli Mohamed

Why does this SQL query return bool(false) in PHP when it works on PHPMyAdmin?

I have the SQL query that is fetching data from multiple columns and fields, with the primary and foreign keys. i am trying to run the code on the search script but on var_dump of the query, i get the query as follows:

SELECT `vendor`.`v_id`
    ,`vendor`.`v_name`
    ,`vendor`.`v_img`
    ,`contacts`.`cont_id`
    ,`contacts`.`cont_addr`
    ,`contacts`.`cont_phn`
    ,`fooditem`.`fi_id`
    ,`fooditem`.`item_name`
    ,`fooditem`.`fi_price`
    ,`fooditem`.`fc_desc`
    ,`vendor`.`v_id`
    ,`vendor`.`v_name`
    ,`vendor`.`v_img`
    ,`contacts`.`cont_id`
    ,`contacts`.`cont_addr`
    ,`contacts`.`cont_phn`
    ,`fooditem`.`fi_id`
    ,`fooditem`.`item_name`
    ,`fooditem`.`fi_price`
    ,`fooditem`.`fc_desc`
FROM vendor
LEFT JOIN `stfood`.`contacts` 
    ON `vendor`.`cont_id` = `contacts`.`cont_id`
LEFT JOIN `stfood`.`fooditem` 
    ON `vendor`.`v_id` = `fooditem`.`v_id`
WHERE `vendor`.`v_name` LIKE '%ven%'
    OR `fooditem`.`item_name` LIKE '%ven%'

and when i proceed forward with the var_dump of the above query i get a bool(false) but this query runs flawlessly on PHP MyAdmin Can anyone tell me why is this so?

The code as requested by "Hirdesh Vishwdewa"

<?php
include_once "init.php";
include_once "functions.php";

//grab the contents to local variable.
$keysearch = $_POST['search_field'];

//sanitize the input contents.
$keysearch = sanitize($keysearch);

//check is the field is empty.
if ($keysearch == "") {
    $errors[] = 'Please enter a search keyword to help you order your favorite food.';
} else {

//if the field is not empty, run the sql query.
$find_query = "SELECT `vendor`.`v_id`,`vendor`.`v_name`,`vendor`.`v_img`,`contacts`.`cont_id`,`contacts`.`cont_addr`,`contacts`.`cont_phn`,`fooditem`.`fi_id`,`fooditem`.`item_name`,`fooditem`.`fi_price`,`fooditem`.`fc_desc`,`vendor`.`v_id`,`vendor`.`v_name`,`vendor`.`v_img`,`contacts`.`cont_id`,`contacts`.`cont_addr`,`contacts`.`cont_phn`,`fooditem`.`fi_id`,`fooditem`.`item_name`,`fooditem`.`fi_price`,`fooditem`.`fc_desc` FROM vendor LEFT JOIN ".DBNAME.".`contacts` ON `vendor`.`cont_id` = `contacts`.`cont_id` LEFT JOIN ".DBNAME.".`fooditem` ON `vendor`.`v_id` = `fooditem`.`v_id` WHERE `vendor`.`v_name` LIKE '%".$keysearch."%' OR `fooditem`.`item_name` LIKE '%".$keysearch."%'";

$result = mysqli_query($con, $find_query);

$rowcount = mysqli_num_rows($result);
?>

//init.php

<?php

if(!isset($_SESSION)) 
    { 
        session_start(); 
    } 
error_reporting(E_ALL);
define("DBNAME","stfood");
global $con;
$servername = "localhost";
$username = "root";
$password = "";

// Create connection
$con = mysqli_connect($servername, $username, $password);

// Check connection
if (!$con) {
    die("Connection failed: " . mysqli_connect_error());
}
?>

//function sanitize

function sanitize($data) {
    return preg_replace('#[^a-z 0-9]#i', '', $data);
}

also to note a point here that if i put the following query for $find_query the code runs as intended. THE FOLLOWING SQL QUERY RUNS:

SELECT `vendor`.`v_id`,`vendor`.`v_name`,`vendor`.`v_img`,`contacts`.`cont_addr`,`contacts`.`cont_phn`,`fooditem`.`item_name`,`fooditem`.`fi_price`,`fooditem`.`fc_desc` FROM ".DBNAME.".`vendor` LEFT JOIN ".DBNAME.".`contacts` ON `vendor`.`cont_id` = `contacts`.`cont_id` LEFT JOIN ".DBNAME.".`fooditem` ON `vendor`.`v_id` = `fooditem`.`v_id` WHERE `vendor`.`v_name` LIKE '%".$keysearch."%' OR `fooditem`.`item_name` LIKE '%".$keysearch."%'

the difference between these 2 queries is that in the query above (that gives me a bool(false)) i have selected the tables primary keys i.e fi_id & cont_id so that i can use them later. When i run this query, it gives me a mysqli_result object. and from there i am able to fetch the results and echo them in the applicable fields. So i want to know that why the query that is under question not working??

Thanks.

via Chebli Mohamed

Query would run directly on MySQL but not through PHP

I have a simple MySQL query

select * from tutor where verified = 0 and alert_by < '2015-08-05' LIMIT 0,1

Now, running this directly through phpMyAdmin provides the desired results, however, when this query is being executed through a set of PHP statements, it doesn't return anything. Below is my code in PHP

$this_date = date("Y-m-d");

$query = "select * from tutor where verified = 0 and alert_by < '$this_date' LIMIT 0,1";

$contact = mysqli_query($conn, $query);
$row = $contact->fetch_array(MYSQLI_ASSOC);

However, the $row is empty, I can't seem to figure this out. I know this seems trivial, but its a little annoying.

Note: Removing "and alert_by < '$this_date'" from the query, works fine.

via Chebli Mohamed

Counting IP addresses in PHP with MySQL

I want to count unique visits to a php file. I currently have following code. Could you expain why it doesnt work?

$ip = $_SERVER['REMOTE_ADDR'];
$geo_url = "http://ift.tt/1esFEZI".$ip."";

$data = file_get_contents($geo_url);
$visitor_location = json_decode($data, true);

$visitorcount = mycustomdb()->query("SELECT ipa FROM visits WHERE ipa = '".$visitor_location['ip']."' LIMIT 1");

if(!empty($visitorcount) {

$filedlupdate = mycustomdb()->query("SOME QUERRY THAT SETS uniquevisits=uniquevisits+1");
$visit = mycustomdb()->query("INSERT INTO visits (ipa) VALUES ('".$ip."')");

}

Basically the idea is to check if the IP is unique. If it is unique then uniquevisits gets a +1 and the ip is added to "ipa". If the ip is present in ipa, then the next check is ignored and nothing is being done. filedlupdate exists and works standalone, but not combined with all the code. Im not sure if I wrote it correctly. Can you spot an error?

via Chebli Mohamed

Database Schema design review (mysql or mongodb)

I am designing DB schema for a project. I have 3 entities:

• professionals
• services
• locations

One professional could provide multiple services (~5) at multiple locations (~1000). One location could have thousands of professionals providing same service and vice-versa.

The queries will be of the form:

1. Find all professionals providing service id-s1 in location id-l1. Sort by popularity/ratings of the professional.
2. Find all locations where a professionalid-p1 provides a service (id-s2).

Caveats - locations also have an associated lat/lng, apart from a standard id. If there are no direct matches for a service in location l1, we may need to search the professionals within radius r from the lat/lng taken given location.

I need to understand two things.

1. Which Database system is better. A relational Mysql or a No-SQL MongoDb (and briefly why).
2. Validation of the schema given below (and suggestions for improvements).

Mysql

professionals | id | name
-----------------------------
                1  | alex
                2  | bob
                3  | charles

services      | id | name
-----------------------------
                1  | stenography
                2  | underwriting
                3  | insurance

locations     | id | lat       | lng         | name
--------------------------------------------------------
                1  | 38.362031 | -98.477500   | office1
                2  | 39.362031 | -99.477500   | office2
                3  | 40.362031 | -100.477500  | office3
                4  | 41.362031 | -101.477500  | office4
                5  | 42.362031 | -102.477500  | office5


services-prof | id | prof_fk | services_fk
--------------------------------------------
                1  | 1       | 1
                2  | 1       | 2
                3  | 1       | 3
                1  | 2       | 1
                2  | 2       | 7
                3  | 3       | 1

location-serv-prof | id | prof_fk | services_fk | location_fk
---------------------------------------------------------------
                      1 | 1       | 1           | 1
                      2 | 2       | 1           | 1
                      3 | 3       | 1           | 1
                      4 | 4       | 1           | 1
                      5 | 5       | 1           | 1

MongoDb

{
    "_id": "t356ah7q",
    "first_name": "Alex",
    "last_name": "Johnson",
    "addresses": {
        "primary_address": {
            "_id": "5765675",
            "lat": "38.362031",
            "lng": "-98.477500"
        },
        "other_addresses": [
            {
                "_id": "5765675",
                "lat": "38.362031",
                "lng": "-98.477500"
            },
            {
                "_id": "5765675",
                "lat": "38.362031",
                "lng": "-98.477500"
            }
        ]
    },
    "services": [
        {
            "service_id": "stenographer",
            "locations": [
                "loc1",
                "loc2",
                "loc3"
            ]
        },
        {
            "service_id": "underwriting",
            "locations": [
                "loc5",
                "loc6",
                "loc7"
            ]
        }
    ]
}

via Chebli Mohamed

How to convert strings from Cyrillic_General_CI_AS to utf8_general_ci in PHP

I need to import some data from MSSQL to MYSQL and I do this programmatically using PHP and PDO. The final lines of code that fetch one last field to be inserted to MYSQL look like this:

$s = $db->prepare("SELECT id FROM object_70912_ 
                         WHERE attr_76453_ = :num AND attr_70954_ = :attr_70954_");
$s->bindParam(':num', $row['building'], PDO::PARAM_STR); 
$s->bindParam(':attr_70954_', $row2['id'], PDO::PARAM_INT);

In this code $row['building'] comes from MSSQL and have this character set - Cyrillic_General_CI_AS, whereas attr_76453_ has this character set in MYSQL - utf8_general_ci. As a result, parameter binding does not work - I can not find appropriate values and this SELECT query allways returns an empty result set - and it is not right, since I checked it. So, I guess that to solve the problem I need to somehow convert data from Cyrillic_General_CI_AS to utf8_general_ci (or it may sound different in terms of PHP).

via Chebli Mohamed

mysql start error on Yosemite, but sqlpro is reading a db

I have several websites, which use PHP and MySQL, these run with no problems as does SQL Pro.

However I have just written a PHP script which runs from the command line and connects to a MySQL database, but I get the following error when trying to run it:

PHP Warning:  mysqli_connect(): (HY000/2002): No such file or directory

I tried restarting MySQL (on the command line and through System Prefs), on the command line I get his error:

ERROR! The server quit without updating PID file

I think the problem is I have 2 MySQL servers installed: /usr/local/mysql/ /usr/local/mysql-5.6.24-osx10.8-x86_64/

My question is, am I correct in thinking that Apache is using one version of MySQL, but PHP on the command line is using another?

How do I find which versions are being used by Apache, MySQL Pro, PHP?

via Chebli Mohamed

PHP mysql to mysqli migration issues (custom functions, procedural style)

Goodmorning

I'm planning to migrate a whole application I made from mysql extension to mysqli, due to next PHP version will not support mysql anymore and I don't want to go fool in the last minutes.

At the moment all page have 2 main inclusions: 1) dbdata.inc.php which contains database connection data 2) function.inc.php which contains most used functions

I'd like to mantain the procedural style also using mysqli extension, but I read that all mysqli functions must receive the connection link as a parameter.

I'm asking for suggestion on the best way (i.e. the most painless solution) to migrate without going mad and without radically rewrite all my php pages.

Actual content of dbdata.inc.php:

$yare_db = mysql_connect($yaredb_host,$yaredb_user,$yaredb_pass) or die("some error warning<br>"); 
mysql_select_db($yaredb_conn); 
mysql_query("SET NAMES 'utf8'");

Most used functions defined in functions.inc.php:

function YQUERY($query) {
    $res = mysql_query($query) or die(mysql_error());
    return $res;
}

function FETCHA($res) {
    $rs = mysql_fetch_array($res);
    return $rs;
}

function NUMROWS($res) {
    $num = mysql_num_rows($res);
    return $num;
}

function AFFROWS() {
    $num = mysql_affected_rows();
    return $num;
}

/* an important function filtering user input texts before passing them to queries */

function msg_safe($string) {
    // some regex and \n to "<br>" substitutions...
    $string = mysql_real_escape($string);
    return $string;
}

Well, the question now is how to migrate:

1) Should i pass the db connection as a function parameter? I.e. something like:

function YQUERY($link,$query) {
    $res = mysqli_query($link,$query);
    return $res;
}

?

2) Should I, instead, define the db connection (defined into included dbdata.inc.php at page start) as GLOBAL variable, inside the function? I.e. something like:

function YQUERY($query) {
     global $link;
     $res = mysqli_query($link,$query);
     return $res;
}

?

3) Should I (it sounds terrific) explicitly declare a new connection inside any custom function? I.e. something like:

function YQUERY($query) {
     $link = mysqli_connect("host","user","pass","db") or die("Error " . mysqli_error($link)); 
     $res = mysqli_query($link,$query);
     return $res;
}

?

4) Other suggestions?

Thanks in advance

via Chebli Mohamed

update data on mysql using ajax

I'm trying to send the id of an image when a user click on it using ajax. Then in the update.php file I'd like to update the id of the image. I'm new on ajax, so I'm not able to figure out my error.

$(document).ready(function(){
$('.show_people_image .love').on('click', function() {

     var id = $(this).attr('id');
     var idpage = "<?php echo $id ?>";
    var sVar1 = encodeURIComponent(id);
    var sVar2 = encodeURIComponent(idpage);
    var sVar3 = "<?php echo $_SESSION['aaa'] ?>";
    var sVar4 = "<?php echo $_SESSION['bbb'] ?>";

     if (sVar1 == sVar2) {
    $.ajax({
        type: "POST",
        url: "update.php",
        data: {lid:sVar1, ml:sVar3, mem:sVar4},
        success: function(data) {
            alert(data);
            },
        error: function () {
        alert('error');
        }  
     });    
     } 
});
});

The update.php file is:

if (isset($_POST['lid']) AND isset($_POST['ml']) AND isset($_POST['mem'])) {

include 'models/connexion_sql.php';


$loveid = $_POST['lid'];

if ($_POST['ml']) {
$str = $_POST['ml'].','.$loveid;
}
else {
$str = $loveid;
}


$sql = 'UPDATE users SET myl= :myl WHERE email = :email';
$req = $bdd->prepare($sql);
$req->bindParam(':email', $_POST['mem'], PDO::PARAM_STR);
$req->bindParam(':myl', $str, PDO::PARAM_STR);
$req->execute();

echo $str;

}  

If I put an echo in the upload.php file before the prepare($sql) the request is successful, otherwise the request fail.

via Chebli Mohamed

Need help interpreting a crash log from QuincyKit

I recently added QuincyKit (a crash log reporter that sits on top of PLCrashReporter) into an app and I received a crash log that I'm having trouble interpreting.

The crash log seems to be inconsistent - it says it was thread 0 that crashed, but the "Last Exception Backtrace" doesn't match with the thread 0 callstack. The last exception points to a table method, but thread 0 callstack indicates an abort occurred during the Quincy manager initialization.

Moreover, the "Last Exception Backtrace" doesn't seem to make much sense taken on it's own - the "canEditRowAtIndexPath" method doesn't even include a call to "removeObjectAtIndex" at all (see below for the method).

Can anyone shed any light onto whether or not I should be paying attention to the "Last Exception Backtrace" or is that a red herring, and I should really be looking into why PLCrashReporter aborted during start up?

Many thanks

Crash log excerpt:

Exception Type:  SIGABRT
Exception Codes: #0 at 0x38362df0
Crashed Thread:  0

Application Specific Information:
*** Terminating app due to uncaught exception 'NSRangeException', reason: '*** -[__NSArrayM objectAtIndex:]: index 7 beyond bounds [0 .. 6]'

Last Exception Backtrace:
0   CoreFoundation                       0x29920fef <redacted> + 126
1   libobjc.A.dylib                      0x37d0cc8b objc_exception_throw + 38
2   CoreFoundation                       0x29833821 -[__NSArrayM removeObjectAtIndex:] + 0
3   DART                                 0x000906b3 -[DeliveryComplete tableView:canEditRowAtIndexPath:] + 262
4   UIKit                                0x2d0a3c25 -[UITableView _canEditRowAtIndexPath:] + 60
5   UIKit                                0x2d0a3a7f -[UITableView _setupCell:forEditing:atIndexPath:animated:updateSeparators:] + 130
6   UIKit                                0x2d0a1179 <redacted> + 2320
7   UIKit                                0x2cf82a31 +[UIView performWithoutAnimation:] + 72
8   UIKit                                0x2d0a0861 -[UITableView _configureCellForDisplay:forIndexPath:] + 336
9   UIKit                                0x2d246383 -[UITableView _createPreparedCellForGlobalRow:withIndexPath:willDisplay:] + 498
10  UIKit                                0x2d24642f -[UITableView _createPreparedCellForGlobalRow:willDisplay:] + 54
11  UIKit                                0x2d23b013 -[UITableView _updateVisibleCellsNow:isRecursive:] + 2258
12  UIKit                                0x2d049657 -[UITableView layoutSubviews] + 186
13  UIKit                                0x2cf73023 -[UIView layoutSublayersOfLayer:] + 546
14  QuartzCore                           0x2c993d99 -[CALayer layoutSublayers] + 128
15  QuartzCore                           0x2c98f5cd <redacted> + 360
16  QuartzCore                           0x2c98f455 <redacted> + 16
17  QuartzCore                           0x2c98edf1 <redacted> + 224
18  QuartzCore                           0x2c98ebdf <redacted> + 434
19  UIKit                                0x2cf6b23b <redacted> + 126
20  CoreFoundation                       0x298e6fed <redacted> + 20
21  CoreFoundation                       0x298e46ab <redacted> + 278
22  CoreFoundation                       0x298e4ab3 <redacted> + 914
23  CoreFoundation                       0x29831201 CFRunLoopRunSpecific + 476
24  CoreFoundation                       0x29831013 CFRunLoopRunInMode + 106
25  GraphicsServices                     0x3100d201 GSEventRunModal + 136
26  UIKit                                0x2cfd5a59 UIApplicationMain + 1440
27  DART                                 0x00015491 _mh_execute_header + 25745
28  libdyld.dylib                        0x38298aaf <redacted> + 2

Thread 0 Crashed:
0   libsystem_kernel.dylib               0x38362df0 __pthread_kill + 8
1   libsystem_c.dylib                    0x382fe909 abort + 76
2   DART                                 0x00122dd7 -[PLCrashReporter enableCrashReporterAndReturnError:] + 1294
3   CoreFoundation                       0x2992131f <redacted> + 630
4   libobjc.A.dylib                      0x37d0cf13 <redacted> + 174
5   libc++abi.dylib                      0x37643de3 <redacted> + 78
6   libc++abi.dylib                      0x376438af __cxa_rethrow + 102
7   libobjc.A.dylib                      0x37d0cdd3 objc_exception_rethrow + 42
8   CoreFoundation                       0x2983129d CFRunLoopRunSpecific + 632
9   CoreFoundation                       0x29831013 CFRunLoopRunInMode + 106
10  GraphicsServices                     0x3100d201 GSEventRunModal + 136
11  UIKit                                0x2cfd5a59 UIApplicationMain + 1440
12  DART                                 0x00015491 _mh_execute_header + 25745
13  libdyld.dylib                        0x38298aaf <redacted> + 2

"canEditRowAtIndexPath" method:

-(BOOL) tableView:(UITableView *)tableView canEditRowAtIndexPath:(NSIndexPath *)indexPath
{
    if (tableView.tag == SIGNER_TABLE_TAG_VALUE)
    {

        RouteStopData *currentStop = [CurrentRoute singleton].selectedStop;
        NSArray *signers = [currentStop signerNamesForStop];

        if (indexPath.row >= [signers count])
        {
            return NO;
        }

        if ([[signers objectAtIndex:indexPath.row] isEqualToString:DARK_DROP_SIGNER_STRING] ||
            [[signers objectAtIndex:indexPath.row] isEqualToString:PAPER_INVOICE_SIGNER_STRING] ||
            [[signers objectAtIndex:indexPath.row] isEqualToString:ADD_NEW_SIGNER_STRING]
            )
        {
            return NO;
        }

        return YES;
    }

    return NO;
}

StoreKit.framework deleted from Library directory

Is there any change to get StoreKit.framework from iOS SDK 8.3 if I accidentally deleted it (without downloading Xcode again)?

It was linked to one of my projects, at some point I needed to delete it but instead of pressing "Remove reference", I pressed "Move to trash" and also "Emptied trash" after that...so now I'm unable to get it back.

Thanks, Alin

UIBezierPath Subclass Initaliser

I'm trying to create a subclass of UIBezierPath to add some properties that are useful to me.

class MyUIBezierPath : UIBezierPath {
   var selectedForLazo : Bool! = false

   override init(){
       super.init()
   }

   /* This doesn't work */
   init(rect: CGRect){
       super.init(rect: rect)
   }

   /* This doesn't work */
   init(roundedRect: CGRect, cornerRadius: CGFloat) {
       super.init(roundedRect: roundedRect, cornerRadius: cornerRadius)
   }

   required init(coder aDecoder: NSCoder) {
       fatalError("init(coder:) has not been implemented")
   }
}

I don't understand how to call a designated initializer without losing information ( e.g call every time super.init() )

Can you help me please?

Parse PFFile download order iOS

I'm storing 5 PFFiles in an array and using getDataInBackgroundWithBlock to download those files from Parse.

The problem is the order at which they appear in the table view cells is different every time, presumably because the files are download at different speeds due to the different file sizes.

for (PFFile *imageFile in self.imageFiles) {
  [imageFile getDataInBackgroundWithBlock:^(NSData *imageData, NSError *error) {
    if (!error) {
      UIImage *avatar = [UIImage imageWithData:imageData];
      [self.avatars addObject:avatar];
      cell.userImageView.image = self.avatars[indexPath.row];
    }
  }];
}

The self.imageFiles array is in the correct order. How do I ensure that the images downloaded are added to the self.avatars array in the same order as the self.imageFiles?

Uploading a file from AVCapture using AFNetworking

I have a video that is captured with AVCapture, and I'm trying to upload with AFNetworking with Swift.

Code:

let manager = AFHTTPRequestOperationManager()
let url = "http://localhost/test/upload.php"
var fileURL = NSURL.fileURLWithPath(string: ViewControllerVideoPath)
var params = [
    "familyId":locationd,
    "contentBody" : "Some body content for the test application",
    "name" : "the name/title",
    "typeOfContent":"photo"
]

manager.POST( url, parameters: params,
    constructingBodyWithBlock: { (data: AFMultipartFormData!) in
        println("")
        var res = data.appendPartWithFileURL(fileURL, name: "fileToUpload", error: nil)
        println("was file added properly to the body? \(res)")
    },
    success: { (operation: AFHTTPRequestOperation!, responseObject: AnyObject!) in
        println("Yes thies was a success")
    },
    failure: { (operation: AFHTTPRequestOperation!, error: NSError!) in
        println("We got an error here.. \(error.localizedDescription)")
})

The code above fails, note that ViewControllerVideoPath is a string containing the location of the video which is: "/private/var/mobile/Containers/Data/Application/1110EE7A-7572-4092-8045-6EEE1B62949/tmp/movie.mov" using print line.... The code above works when Im uploading a file included in the directory and using:

 var fileURL = NSURL.fileURLWithPath(NSBundle.mainBundle().pathForResource("test_1", ofType: "mov")!)

So definitely my PHP code is fine, and the problem lies with uploading that file saved on the device, what am I doing wrong here?

ReactiveCocoa Issue

I'm attempting to use ReactiveCocoa in my project to handle the population of a UITableView.

When I load the data if none is available, I want to set the hidden property of tableView.backgroundView to false. Here's what I have so far:

func loadData() {
    let dataSource = tableView.dataSource as! BlockedTableViewDataSource
    let load = dataSource.load # RACSignal

    load.map {
        return ($0 as! [AnyObject]).count > 0
    }.startWith(true).distinctUntilChanged().setKeyPath("hidden", onObject: tableView.backgroundView!)

    load.subscribeError({ error in
        println(error)
    }, completed: {
        self.tableView.reloadData()
        self.refreshControl?.endRefreshing()
    })
}

This however errors out saying that I need to wait for the network request to finish. I'm using Parse to fetch the data but I'm thinking that my ReactiveCocoa code just isn't set up correctly and is causing this error. If I comment out the load.map... portion the table populates as expected.

How would one going about implementing this in the "Reactive Way"?

character jumps more than anticipated?

my problem is that when i jump at the start of my game it jumps three times when I only want to jump 2 times. What the code does is at the start a bool startgame is set to false and when clicked start moving the shape and starts the animation of dan, the character of my game. In the update method whenever the shape and dan touch the isjumping boolean variable is set to false so that when you click, dan jumps and then sets the didjumponce boolean to true so that it can jump for a second time. but for some reason at the start of the game it allows dan to jump three times. i tried setting a touch counter(timestapped) to 0 and every time there is a tap it adds on 1 so that when it checks if you can jump a second time is doesn't allow it. but it still does. thanks :)

@implementation GameScene
bool isJumping;
bool isPlaying;
bool didJumpOnce;
bool startGame = false;
int timesTapped = 0;

-(void)touchesBegan:(NSSet *)touches withEvent:(UIEvent *)event
{
    /* Called when a touch begins */

    timesTapped++;

    // checks to see if the game has started, if it hasn't, starts dan's animation
    if (startGame == false)
    {
        startGame = true;
        [self dansAnimation];
    }
    else if (isJumping == false)
    {
        isJumping = true;
        didJumpOnce = true;

        dan.physicsBody.velocity = CGVectorMake(0, 0);
        [dan.physicsBody applyImpulse: CGVectorMake(0, 55)];
    }
    else if (didJumpOnce == true && timesTapped != 2)
    {
       didJumpOnce = false;

        dan.physicsBody.velocity = CGVectorMake(0, 0);
        [dan.physicsBody applyImpulse: CGVectorMake(0, 55)];
    }
}

-(void)update:(CFTimeInterval)currentTime
{
    /* Called before each frame is rendered */

   if (startGame == true)
   {
        shape.position = CGPointMake(shape.position.x - 7, shape.position.y);
   }

   if (CGRectIntersectsRect(dan.frame, shape.frame))
   {
        isJumping = false;
   }
}
@end