how can I change background color of dynamically filled table row based on the value of one of the cells?

I have a table being filled dynamically from mysql database. One of the columns in the table is a date. I want to change the background color of the row if the date is more than 3 days old. Trying to figure this out, I have figured out the formula and on a test.php page I can make it work with a textbox. I need to know how to apply it to a table row.

function call_log_date($enter_date)
{
    $today      = strtotime(date("m/d/Y"));
    $other_date = strtotime($enter_date) . "<br>";
    // returns number of days between dates(because of abs, will always
    // return positive number)
    $diff = ceil(abs($today - $other_date) / (60 * 60 * 24));
    if ($diff > 3) {
        $bg_color   = "#FFFF00"; //yellow
        $font_color = "#FF0000"; //red
    } else if ($diff > 1 && $diff <= 3) {
        $bg_color   = "#FFFFFF"; // white
        $font_color = "#FF0000"; //red
    } else {
        $bg_color   = "#FFFFFF"; // white
        $font_color = "#000000"; //black
    }

    return $bg_color . $font_color;
}

Above is the function that I am using that works for a textbox

How to remove text between 2 characters using query in MYSQL

I have table with field called description which contains text like following

|Description                              |
|-----------------------------------------|
|This is a text <a>blah</a> <br/> <img /> |
|This is <b>second</b> <a>row</a>         |

I would like to remove everything between "<" and ">"

Required Output

|Description                              |
|-----------------------------------------|
|This is a text blah                      |
|This is second row                       |

Is there anyway I can achieve this using MYSQL query. I cant currently use Stored procedure for the same as I don't have rights. I have requested for the access to use stored procedure but that will take some time and I have to finish this bit urgently.

Thanks.

Update: my question is similar to Remove string between 2 characters from text string but I want it to be done in MYSQL query.

SQL - Display the minimum value of an average price

My database contains a list of products and finishes. I have been able to successfully average the price of each finish option, but I only want to display the lowest priced option.

Here is a sample of my data.

Table 1 - product_t

ProductID   ProductLineID   ProductDescription  ProductFinish   ProductStandardPrice    ProductOnHand
1   1   "Cherry End Table"  Cherry  175.00  0
2   1   "Birch Coffee Tables"   Birch   200.00  0
3   1   "Oak Computer Desk" Oak 750.00  0
4   1   "Entertainment Center"  Cherry  1650.00 0

This query results in a list of each available product finish and the average price of an item that has the finish.

SELECT ProductFinish, AVG(ProductStandardPrice) as AveragePrice
FROM product_t
WHERE ProductFinish IS NOT NULL
GROUP BY ProductFinish;

However, I only want to display the lowest price that results from this query. I attempted this query, but it will not execute.

 SELECT ProductFinish, MIN (AVG(ProductStandardPrice)) as AveragePrice
 FROM product_t
 WHERE ProductFinish IS NOT NULL
 GROUP BY ProductFinish;

Thanks in advance for any help. It is much appreciated.

PDOException SQLSTATE[HY000] [2002] No such file or directory. Whilst running laravel via isolated docker containers

Trying to run laravel via docker containers. So, isolated containers include artisan, mariadb, phpfpm, phpmyadmin, data, and storage containers.

These containers are connected via links, the data container stores laravel files, and the storage container is meant to store the database files; thus, both act as the volume to other containers.

The mariadb container runs of the stock mariadb container provided on the docker hub repository. Artisan and phpfpm containers are based on the image provided by dylanlindgren on the docker hub repository.

The artisan container is linked to the db container, with the following command:

docker run --privileged=true --volumes-from myapp-data  --link myapp-db --rm ayoolaosun/docker-laravel-artisan. 

However, when running the artisan migrate command, the following error pops up:

"PDOException SQLSTATE[HY000] [2002] No such file or directory". 

I recently learnt about containers and am very new to devOps, so I am not sure what to tweak in the containers to solve the problem. Thanks, any tips will do.

Cannot grant privileges as root@localhost mysql

I've been digging around trying to fix this issue however i'm coming up short. I'm trying to grant all privileges on *.* to 'root'@'%' with grant option on a mariadb server, as a current user of 'root'@'localhost' and continually get access denied. SHOW GRANTS FOR CURRENT_USER()G; *************************** 1. row *************************** Grants for root@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*sanitized' WITH GRANT OPTION *************************** 2. row *************************** Grants for root@localhost: GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION i have tried FLUSH PRIVILEGES several times as well as restarting the service. +--------------------------+---------------+------------+------------+ | host | user | Grant_priv | Super_priv | +--------------------------+---------------+------------+------------+ | localhost | root | Y | Y | MariaDB [(none)]> select current_user(); +----------------+ | current_user() | +----------------+ | root@localhost | +----------------+ select user,host from mysql.user; +---------------+--------------------------+ | user | host | +---------------+--------------------------+ | root | % |

RDLC Reporting in MVC5 EF6 Code First

I was trying to make reports of Chart Of Accounts in ASP.net using Code first approach and RDLC. I got the following error (bunch of same error for different models)

The mapping of CLR type to EDM type is ambiguous because multiple CLR types match the EDM type 'AccountCodesAssign'. Previously found CLR type 'FYP.Models.AccountCodesAssign', newly found CLR type 'FYP.Edmx.AccountCodesAssign'.

The schema specified is not valid in other words. What I did was that I included the EDMX file of my database into my project, made the DataSet from it and designed an RDLC report.

I used the following code in ReportViewer.aspx.cs file:

 protected void Page_Load(object sender, EventArgs e)
       {
            PopulateReportData();   
        }
        private void PopulateReportData() {
            List<FYP.Edmx.COA_CtrlCode> ctrl = null;
            using (FYPEntities db = new FYPEntities()) {
                ctrl = db.COA_CtrlCode.ToList();
            }
            ReportViwer1.LocalReport.DataSources.Clear();
            ReportDataSource rd = new ReportDataSource("DataSetca", ctrl);
            ReportViwer1.LocalReport.DataSources.Add(rd);
            ReportViwer1.LocalReport.Refresh();
        }

Please help if you can...

Opencart report module Query

I need a report form so I'm trying to list the category, customername, email, telephone etc. Except for the category, everything works perfectly when I click the filter button. This is my query:

SELECT MIN(o.date_added) AS date_start, MAX(o.date_added) AS date_end,
       COUNT(*) AS `orders`, SUM((SELECT SUM(op.quantity) FROM `oc_order_product`
       op LEFT JOIN `oc_product_to_category` pc ON (op.product_id = pc.product_id)
       WHERE op.order_id = o.order_id GROUP BY op.order_id))
       AS products, SUM((SELECT SUM(ot.value) FROM `oc_order_total`
       ot WHERE ot.order_id = o.order_id AND ot.code = 'tax' GROUP BY ot.order_id))
       AS tax, SUM(o.total) AS `total` FROM `oc_order` o
       WHERE o.order_status_id > '0' AND DATE(o.date_added) >= '2015-10-01' AND
       DATE(o.date_added) <= '2015-10-22' And o.shipping_postcode = '4545456' AND
       pc.category_id = '72'

Please anybody correct my query.

MySQL inner join coding Challenge

Julia asked her students to create some coding challenges. Write a query to print the hacker_id, name, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.

Input Format

The following tables contain challenge data:

Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.

Challenges: The challenge_id is the id of the challenge, and hacker_id is the id of the student who created the challenge.

For Sql performances, several equals or one between

For a new developement, I will have a big SQL table (~100M rows). 4 fields will be used to query the data.

Is it better to query one concatenated field with between or several equals ?

Exemple :

MainTable

PkId | Label | FkId1 | FkId2 | FkId3 | FkId4
1    | test  | 1     | 4     | 3     | 1

Datas in Fk tables are static, example :

FkTable1

Id | Value
1  | a
2  | b
3  | c 

To query the datas, the classic sql query is :

select Label, FkId1, FkId2, FkId3, FkId4 
from MainTable
where FkId1=1 and FkId2=2 and FkId3 in(2, 3)

The idea to optimize performance is to add one field "UniqueId" calculated backend before the insert :

UniqueId = FkId1*1000000 + FkId2*10000 + FkId3*100 + FkId4

PkId | Label | FkId1 | FkId2 | FkId3 | FkId4 | UniqueId
1    | test  | 1     | 4     | 3     | 1     | 1040301

select Label, FkId1, FkId2, FkId3, FkId4 
from MainTable
where UniqueId between 1020200 and 1040000

Moreover, with the UniqueId field, an index on this field only will be sufficient.

What do you think ?

Thanks

SSIS Execute SQL Task Editor query failed to parse

I have the following script in a Execute SQL Task Editor. I have two parameters that I have mapped to the question marks. The query parses when I set @ClientCode and @PEK to something else. Why does my query not parse with the ? parameters? The full error is "The query failed to parse. Syntax error, permission violation, or other nonspecific error"

declare @ClientCode varchar(100)
declare @PEK int
set @ClientCode = ?
set @PEK = ?


if((@ClientCode != '') and (@ClientCode is not null))
begin
    exec        portal.GetClients @ClientCode
end

else if((@PEK != 0) and (@PEK != '' ))
begin

    select      distinct c.Id, c.Name, c.Code
    from        Features.map.ProfileAreasManagementAreas pama INNER JOIN
                ClientDW.dbo.ManagementAreas ma ON pama.ManagementAreaKey = ma.ManagementAreaKey INNER JOIN
                ClientDW.dbo.Clients c ON ma.ClientKey = c.ClientKey
    where       pama.PublishEventKey = @PEK
end

else 
begin  
    select      top 1 PublishEventKey
    from        Features.publish.PublishEvents
    order by    PublishDate  desc

end 

Oracle stored procedure works with ADO.NET but with OrmLite throws exception?

I have a following stored procedure:

create or replace PROCEDURE PRODUCT_DETAILS(p_code IN VARCHAR2, 
                                            cursorParam OUT SYS_REFCURSOR)
IS
BEGIN
  OPEN cursorParam FOR
  select str_auth_code, str_name
  from strs
  where str_auth_code = p_code;
END;

How can I call it with OrmLite? I've tryied:

connection.SqlList<Product>(@"EXEC PRODUCT_DETAILS @p_code", new { p_code = code });

but it throws an exception ORA-01036: illegal variable name/number

I just tried to do it with plain old ADO.NET and it worked:

using (var conn = new OracleConnection(connectionString))
{
    OracleCommand cmd = new OracleCommand();
    cmd.Connection = conn;
    cmd.CommandText = "PRODUCT_DETAILS";
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add("p_code", OracleType.NVarChar).Value = redemptionCode;
    cmd.Parameters.Add("cursorParam", OracleType.Cursor);
    cmd.Parameters["cursorParam"].Direction = ParameterDirection.Output;

    conn.Open();
    OracleDataReader dr = cmd.ExecuteReader();

    while (dr.Read())
    {
        Console.WriteLine(dr["Name"]);

    }
    conn.Close();
}

But I can't figure out how to do the same task with OrmLite.

Combine two queries with monthly average

I need to put together the results of these two queries into a single return with the following structure:

"date", avg(selic."Taxa"), avg(titulos."puVenda")

Partial structure of tables:

selic
"dtFechamento" date,
"pTaxa" real

titulos
"dtTitulo" date,
"puVenda" real,
"nomeTitulo" character(30)

Query table selic:

select to_char("dtFechamento", 'YYYY-MM') as data, avg("pTaxa")
from "selic"
group by data
order by data

Query table titulos:

select to_char("dtTitulo", 'YYYY-MM') as data, avg("puVenda")
from "titulos"
where "nomeTitulo" = 'LFT010321'
group by data
order by data

I tried a subquery, but it returned the fields next to each other and can not muster.

select *
from (select to_char("dtFechamento", 'YYYY-MM') as data, avg("pTaxa")
from "selic"
group by data
order by data) as selic,
(select to_char("dtTitulo", 'YYYY-MM') as data, avg("puVenda")
from "titulos"
where "nomeTitulo" = 'LFT010321'
group by data
order by data) as LFT010321;

Is there a way to compile jar files to OS X binary code?

I made a java program which worked perfectly fine on java(it should..) but I encountered one problem which was the encryption of my project because I could easily decompile it with a jar-decompiler like jd-gui.

In my project I have a connection to my online MySQL database and to connect to it it needs the user, password and stuff. As I wanted to protect it I looked up ways to encrypt class files.

Basically I wan't to protect some data in the code.

I know about Zelix Klassmaster, Class-Loader(loading up encrypted class files) and Excelsior JET.

I am really happy with Excelsior JET, because the user does not need java!

There are multiple answers I would be happy about:

How to: String encryption etc. like Zelix Klassmaster

A way to hide the MySQL connection user data(username, table, password, host..) in the class file

An application which does the same thing like Excelsior JET just for MAC OS

Running multiple SQL queries is not working

I am trying to run multiple queries in 1 php:

$sql = "UPDATE table1 SET column='1' WHERE id='1'";
$sql.= "INSERT INTO table2(column1, column2) VALUES ('1', '2')";
if (!$mysqli->multi_query($link, $sql)) {

When I run this I get a error. Does anybody know what is wrong with my script?

Here is my full script:

<?php
$link = mysqli_connect("localhost", "root", "", "db");                   
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
$sql = "UPDATE table1 SET column='1' WHERE id='1'";
$sql.= "INSERT INTO table2(column1, column2) VALUES ('1', '2')";
if (!$mysqli->multi_query($link, $sql)) {

echo "Success";
} else {
 echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}

// Close connection
mysqli_close($link);
?>

Edit 1:

I get the following error: Notice: Undefined variable: mysqli in ......../script.php on line 24

Fatal error: Call to a member function multi_query() on a non-object in ......../script.php on line 24

mysql for percentage between rows

I have some sql that looks like this:

SELECT 
stageName,
count(*) as `count`

FROM x2production.contact_stages
WHERE FROM_UNIXTIME(createDate) between  '2016-05-01' AND DATE_ADD('2016-08-31', INTERVAL 1 DAY) 
    AND (stageName = 'DI-Whatever' OR stageName = 'DI-Quote' or stageName = 'DI-Meeting')
Group by stageName
Order by field(stageName, 'DI-Quote', 'DI-Meeting', 'DI-Whatever')

This produces a table that looks like:

+-------------+-------+
|  stageName  | count |
+-------------+-------+
| DI-quote    |  1230 |
| DI-Meeting  |   985 |
| DI-Whatever |   325 |
+-------------+-------+

Question:

I would like a percentage from one row to the next. For example the percentage of DI-Meeting to DI-quote. The math would be 100*985/1230 = 80.0%

So in the end the table would look like so:

+-------------+-------+------+
|  stageName  | count | perc |
+-------------+-------+------+
| DI-quote    |  1230 | 0    |
| DI-Meeting  |   985 | 80.0 |
| DI-Whatever |   325 | 32.9 |
+-------------+-------+------+

Is there any way to do this in mysql?

Here is an SQL fiddle to mess w/ the data: http://sqlfiddle.com/#!9/61398/1

Why is the same SQL sometimes report errors, some time it runs fine?

This is the SQL statement:

select 
    ua.*, uuu.user_name 
from
    (select 
         ud.dev_id, 
         (select uud.user_id as user_id 
          from user_device uud 
          where ud.dev_id = uud.dev_id and assigned = 1) user_id,
         (select count(1) 
          from user_device du 
          where du.dev_id = ud.dev_id) user_number, 
         de.license
     from 
         user_device ud 
     inner join 
         device de on ud.dev_id = de.dev_id 
     where ud.user_id = 'XXXXXX') ua 
left join 
    user_info uuu on uuu.user_id = ua.user_id 

Execute the same SQL, it sometimes reports this error, but sometimes it runs just fine.

The error :

and this is what I want (with another user_id yesterday)

How to select entire rows based on distinct columns

I am doing this in spark

cityId  PhysicalAddress      EmailAddress         ..many other columns of other meta info...   
1       b st                 something@email.com   
1       b st                 something@email.com   <- some rows can be entirely duplicates
1       a avenue             random@gmail.com
2       c square             anything@yahoo.com
2       d blvd               d@d.com

There is no primary key on this table and I want to grab one random row based on each distinct cityId

e.g. This is a correct answer

cityId  PhysicalAddress      EmailAddress        ..many other columns 
1       b st                 something@email.com   
2       c square             anything@yahoo.com

e.g. this is also an correct answer

cityId  PhysicalAddress      EmailAddress       ..many other columns 
1       a avenue             random@gmail.com
2       c square             anything@yahoo.com

One way that comes to mind is to use a group by. However, that requires me to use an aggregate function on the other column. (such as min()). Whereas, I just want to pull out an entire row (doesn't matter which one).

How can I select all rows which have been inserted in the last day?

I have a table like this:

// reset_password_emails
+----+----------+--------------------+-------------+
| id | id_user  |        token       | unix_time   |
+----+----------+--------------------+-------------+
| 1  | 2353     | 0c274nhdc62b9dc... | 1339412843  |
| 2  | 2353     | 0934jkf34098joi... | 1339412864  |
| 3  | 5462     | 3408ujf34o9gfvr... | 1339412894  |
| 4  | 3422     | 2309jrgv0435gff... | 1339412899  |
| 5  | 3422     | 34oihfc3lpot4gv... | 1339412906  |
| 6  | 2353     | 3498hfjp34gv4r3... | 1339412906  |
| 16 | 2353     | asdf3rf3409kv39... | 1466272801  |
| 7  | 7785     | 123dcoj34f43kie... | 1339412951  |
| 9  | 5462     | 3fcewloui493e4r... | 1339413621  |
| 13 | 8007     | 56gvb45cf3454g3... | 1339424860  |
| 14 | 7785     | vg4er5y2f4f45v4... | 1339424822  |
+----+----------+--------------------+-------------+

Each row is an email. Now I'm trying to implement a limitation for sending-reset-password email. I mean an user can achieve 3 emails per day (not more).

So I need an query to check user's history for the number of emails:

SELECT count(1) FROM reset_password_emails WHERE token = :token AND {from not until last day}

How can I implement this:

. . . {from now until last day}

---

Actually I can do that like: NOW() <= (unix_time + 86400) .. But I guess there is a better approach by using interval. Can anybody tell me what's that?

SQL Server table creation error : There are no primary or candidate keys in the referenced

A very basic script is driving me crazy. It says it cant find a referenced key. Not idea what it's all about. I'm am using SQL SERVER 2014 and this script is for the creation of my database tables. I'm trying to make the id_TABLE_1 in the table TABLE_2 reference the id of the table TABLE_1.

CREATE TABLE TABLE_1
(
    id int identity,
    email varchar(50) not null,

    constraint PK_TABLE_1 primary key (id,email)
)
GO

CREATE TABLE TABLE_2
(
    id int identity,
    id_TABLE_1 int not null,

    constraint PK_TABLE_2 primary key (id),
    constraint FK_TABLE_2 foreign key (id_TABLE_1) 
        references TABLE_1(id) on delete cascade
)
GO

The error is :

Msg 1776, Level 16, State 0, Line 32
There are no primary or candidate keys in the referenced table 'TABLE_1' that match the referencing column list in the foreign key 'FK_TABLE_2'.

Msg 1750, Level 16, State 0, Line 32
Could not create constraint or index. See previous errors.

Can you help me here ?

SELECT DISTINCT count() in Microsoft Access

I've created a database where we can track bugs we have raised with our developers (Table: ApplixCalls) and track any correspondence related to the logged bugs (Table: Correspondence).

I'm trying to create a count where we can see the number of bugs which have no correspondence or only correspondence from us. This should give us the visibility to see where we should be chasing our developers for updates etc.

So far I have this SQL:

SELECT DISTINCT Count(ApplixCalls.OurRef) AS CountOfOurRef
    FROM ApplixCalls LEFT JOIN Correspondence ON ApplixCalls.OurRef = Correspondence.OurRef
    HAVING (((Correspondence.OurRef) Is Null) 
        AND ((ApplixCalls.Position)<>'Closed')) 
    OR ((ApplixCalls.Position)<>'Closed') 
        AND ((Correspondence.[SBSUpdate?])=True);

I'm finding that this part is counting every occasion we have sent an update, when I need it to count 1 where OurRef is unique and it only has updates from us:

  OR ((ApplixCalls.Position)<>'Closed') 
        AND ((Correspondence.[SBSUpdate?])=True);

Hopefully that makes sense...

Is there a way around this?

I'm unable to connect to MySql DB remotely from my C# application

How can I connect to my MySql DB on godadday from my C# application? I tried looking for settings to enable this and cannot find it. Also what IP address should I be using? I'm connecting to my site using the IP address given to me with and without port 3306 to no success.

ERROR: I x'ed out the server ip and user

An unhandled exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll

Additional information: Authentication to host 'xxx.xx.xxx.xxx' for user 'xxxxx' using method 'mysql_native_password' failed with message: Access denied for user 'xxxxx'@'c-73-12-216-99.hsd1.ca.comcast.net' (using password: YES)

CODE:

public static bool Connect(string userName, string password, string server, string database)
    {
        MySqlConnectionStringBuilder conn_string = new MySqlConnectionStringBuilder();
        conn_string.Server = server;
        conn_string.UserID = userName;
        conn_string.Password = password;
        conn_string.Database = database;
        conn_string.Port = 3306;

        string con = conn_string.ToString();
        using (MySqlConnection conn = new MySqlConnection(con))
        using (MySqlCommand cmd = conn.CreateCommand())
        {                    
            conn.Open();                
            cmd.ExecuteNonQuery();
        }

        return true;
    }

How to properly clean html post to save in mysql

I'm trying to create a blog system, I'm at a point where i want to save the blog post data into mysql but little confuse how to clean or sanitize the data here is what I've tried function test_input($data) { $data = trim($data); $data = stripslashes($data); return $data; } Now when i posted blog data through ajax it looked like <h1 class="text-center"> Write the titles of article here </h1> then i clean the post data & echo it echo $title = mysqli_real_escape_string($connecDB, test_input($_POST['page__title'])); Then here is what I got in response rn Write the titles of article herern I want to know why I'm getting these rn in the response how can i get rid of them which is the best way of cleaning html post Note: i want to save the data as html in mysql I already have a regex that will find style & script tags & remove it, i also tried removing strip slashes & tried adding strip_tags also but still I'm getting rn

Large SQL query eats memory simply by retrieving data

I have a PHP script which calls a "large" SQL query - "large" in term of retrieved records (close to 1 million). At first it worked fine and now after the database reached this size I've been getting a "Killed." message after a while. A quick check showed that the PHP script was simply out of memory.

Originally I thought this was an issue with the code, that I somehow created an array that grows as more data is retrieved from the query, but that is not the case. After commenting all the code except

for ($i = 0; $i < $result->num_rows; $i++) {
            $data = $result->fetch_assoc();
            echo "Using ", memory_get_peak_usage(1), " bytes of ram.n";
}

it still shows the memory increasing over time until - again, the script is "Killed.". Any idea how to retrieve records from the database in a way that it will not constantly increase the memory footprint? or is there no way around it and I just need to increase the RAM available for the script?

Thanks!

Where Command not Working while fetching a data

<?php

$hostname="localhost";
$user="root";
$pass="";
$dbname="testdb";

$db=mysqli_connect($hostname,$user,$pass,$dbname);
if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
}

$username = $_POST["Email"];
$password = $_POST["Password"];
$statement=mysqli_prepare($db,"SELECT * FROM login WHERE Email = ? AND Password = ?");
mysqli_stmt_bind_param($statement,"s",$username,$password);
mysqli_stmt_execute($statement);

 mysqli_stmt_store_result($statement);
 mysqli_stmt_bind_result($statement,$username,$password);

 $response = array();
  $response["success"] = false;

while(mysqli_stmt_fetch($statement)){
    $response["success"] = true;
$response["username"] = $username;
$response["password"] = $password;

}

echo json_encode($response);

?>

So i am trying to create a baisc Login Page and this is my code. The Datatype of Email and Password are both Varchar(40) in the table and Email is my primary Key.

It is returning succes as false even though the data is present. Basically it is not doing the query part efficiently. I have tried coding it in all way possible, OOP and Procedural ,mysql and mysqli and everything else. Can someone help me out here .

Laravel 5.1: handle joins with same column names

I'm trying to fetch following things from the database:

• user name
• user avatar_name
• user avatar_filetype
• complete conversation_messages

with the following query:

    static public function getConversation($id)
{
    $conversation = DB::table('conversation_messages')
        ->where('belongsTo', $id)
        ->join('users', 'conversation_messages.sender', '=', 'users.id')
        ->join('user_avatars', 'conversation_messages.sender', '=', 'user_avatars.id')
        ->select('users.name', 'conversation_messages.*', 'user_avatars.name', 'user_avatars.filetype')
        ->get();
    return $conversation;
}

It works fine so far, but the avatar's column name is 'name' like the column name from the 'users' table. So if I'm using this query the to get the output via $conversation->name, the avatar.name overwrites the users.name

Is there a way to rename the query output like the mysql "as" feature at laravel 5.1?

For example:

$conversation->avatarName

$conversation->userName

How can I select all columns as 1 column?

How Do I get the result as below in single Column with '-' Seperated:-

Details

AAAA-A-11111

Here - Details is Column Name

Create Table Users1
(
FName varchar (50),
LName Varchar (50),
Number varchar(10),
CreateDate Datetime
)    

INSERT INTO Users1 Values ('AAAA','A','11111','06-25-2016 00:10')
INSERT INTO Users1 Values ('BBBB','B','22222','06-25-2016 01:18:000')
INSERT INTO Users1 Values ('CCCC','C','33333','06-25-2016 06:10:000')
INSERT INTO Users1 Values ('DDDD','D','44444','06-25-2016 06:10:000')
INSERT INTO Users1 Values ('EEEE','E','55555','06-25-2016 23:10:000')
INSERT INTO Users1 Values ('FFFF','F','66666','06-25-2016 00:01:000')
INSERT INTO Users1 Values ('FFFF','G','77777','06-26-2016 00:01:000')
INSERT INTO Users1 Values ('FFFF','H','88888','06-26-2016 16:01:000')
INSERT INTO Users1 Values ('FFFF','F','99999','06-26-2016 23:01:000')

This is my Query :-

SELECT FName, '-', LName, '-', Number FROM Users1

Result is '-' as separate Column , which I don't want, Please help me

Mongoosejs: Cleanest way to select rows with `limit` and `where`, then get count of total

When I work with PHP and MySQL and I need to select number of rows and then count total (for pagination, for exmaple) I do something like this:

$rows = DB::getRows('
    SQL_CALC_FOUND_ROWS
    select * 
    from posts 
    where mode = "published" 
    order by `dateCreated` 
    limit '.$limit.'
');

and then

$totalRows = DB::getOne('
    SELECT FOUND_ROWS()
');

Easy and simple.

Recently I started to work on nodejs/mongo project and I wonder what is the cleanest way to achieve the same goal with mongoose.

Now my code looks like this:

var result = {};

var sort = {dateCreated: -1};
var skip = limit * (page - 1);

modelClass.find(function (err, items) {

    result.items = items;

    modelClass.find().where('mode').equals('published').count(function (err, total) {
        result.totalItems = total;
        callback([], result);
    });

}).where('mode').equals('published').sort(sort).limit(limit).skip(skip);

What I do not Like is that I am repeating myself in .where('mode').equals('published').

Is there a better way to do it?

Elasticsearch Nested Indexing using Pentaho Data Integration

I need your help. I have problem indexing my data to elasticsearch type nested from mysql. To do indexing to elastic search type nested I must have object data. I have been used modified javascript value to modify data to object. But, it doesn’t work cause it doesn’t support type object in output fields. I have posts and comments table which posts is parent and comments is child. I need to transform posts and comments like this:

{ "id" : "3", “title”:”blablabla”, “description” : “this is description of blablabla“, "publish_at":"1457230597", “comments” : [ { “id” : "1", “post_id” : "3", “user_id” : "3", “comment” : “this is comment 1”, }, { “id” : "1", “post_id” : “3”, “user_id” : “5”, “comment” : “this is comment 2”, } ] }

This is just sample, so please ignore about name field and content. I already read about data type supported in pentaho. But none support to object. I believe still there is one step that I don't know yet. Any recommendation what should I do?

How to join Multiple table using hibernate criteria where entity relationship is not direct?

I have three entity. those are -

@Entity
@NoArgsConstructor(access = AccessLevel.PUBLIC)
@AllArgsConstructor(access = AccessLevel.PUBLIC)
public
@Data
class Organization implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
@Column(nullable = false, unique = true)
private String name;
}

@Entity
@NoArgsConstructor(access = AccessLevel.PUBLIC)
@AllArgsConstructor(access = AccessLevel.PUBLIC)
public
@Data
class Book implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(nullable = false)
private String name;
@ManyToOne
private Organization organization;
}


@Entity
@NoArgsConstructor(access = AccessLevel.PUBLIC)
@AllArgsConstructor(access = AccessLevel.PUBLIC)
public
@Data
class Account implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(nullable = false)
private String name;
@ManyToOne
private Book book;
}

In these three entity I would like to perform following sql-

SELECT acc.name, acc.id
FROM account acc
JOIN book b on acc.book_id = b.id
JOIN organization org on b.organization_id = org.id
WHERE org.name = 'XYZ'

In this case Account entity has no relation with the Organization entity directly. Account entity has the relation via Book. How can I achieve this using hibernate criteria dynamic query ?

Oracle MERGE - if not matched then update if condition passes

I need to merge some values into a table, updating a field when a row with the specified key already exists, or inserting a new row if it doesn't exist.

This is my table:

profiles(name, surname, active);

where:

name    VARCHAR2(30)
surname VARCHAR2(30)
active  NUMBER(1)

name and surname -> composite primary key

I'm using this query:

MERGE INTO profiles USING (
    SELECT
        'Mark' myName,
        'Zibi' mySurname,
        '1'    myActive
   FROM DUAL
) ON (
   name = myName
   AND surname = mySurname
)
WHEN MATCHED THEN
    UPDATE SET
        active = myActive
WHEN NOT MATCHED THEN
    INSERT (
        name,
        surname,
        active
    ) VALUES (
        myName,
        mySurname,
        myActive
    );

It works, but it updates a record even if active is already set to 1.

What I would like to do is something like this:

WHEN MATCHED THEN
    IF(active != myActive)
        UPDATE SET
            active = myActive
    ELSE
        RAISE CUSTOM EXCEPTION
WHEN NOT MATCHED THEN
    INSERT [...]

Is that possible? AFAIK I cannot put an if like this into a MERGE statement, so how could it be done?

SQL - Split One Table to Two And Link

I am using SQL Server 2014. I looked up solution by Joel Coehoorn for this question but it did not work for me.

I have a table for clients which I renamed to Aac_Client which has both client information and address information as columns. I want to move Address to a new table Address to organise things and keep other remaining things at Ac_Client. I am trying to do the following:

Note the relationship between Address and Client is One to One.

BEGIN TRANSACTION
   DECLARE @DataID int;

   -- Insert Address
    INSERT INTO Address ([StreetNumber],[StreetName] ,[StreetAddress2] ,[Unit] ,[City] ,[State] ,[Zip] ,[County])
      SELECT  [StreetNumber], [StreetName], [StreetAddress2], [Unit] ,[City] ,[State] ,[Zip] ,
      NULL AS [County]
      FROM Aac_Client

    -- Get Address Id
   SELECT @DataID = scope_identity();

    -- Insert Client    
    INSERT INTO Ac_Client ( AddressId, Name, Phone, Contact )
        SELECT  @DataID AS AddressId, Name , Phone, Contact FROM Aac_Client

COMMIT

But the problem is INSERT is carried at once and I get the last ID of address in SELECT @DataID = scope_identity();

run php script on same site

Hello I wrote a small website. If i click on the Button a php script will start and give me some results of my sql database back. i want run the script on the site where my button is and not on a new site.

index.php

<html>
<head>
</head>
<body>
...
...
<form action="function.php" method="get">
            <input type="submit" value="Button">
        </form>
...
...
</body>
/<html>

function.php

<html>
<body>
<?php
require_once ('connection.php');
$db_link = mysqli_connect (
    MYSQL_HOST,
    MYSQL_USER,
    MYSQL_PASS,
    MYSQL_DB
);

$sql ="SELECT * FROM table" ;
if ($result = $db_link->query($sql) ) {
    echo "<p>Your login details:  </p><table>";
    while ($row = $result->fetch_array() ) {
        echo "<tr>";
        echo "<td>", "$row[0]","</td>";
        echo "<td>", "$row[1]","</td>";
        echo "</tr>";
    }
    echo "</table>";
    $result->close();
}
else {
    echo "Problem SQL";
}
$db_link->close();
?>
</body>
    </html>

Resolution: This table does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available

This is not a question but a self generated problem/question and solution that I found working. I thought it would be decent courtesy to share since there was not a complete working solution I could find.

The error generated in phpMyAdmin is:

"This table does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available."

There are several working solutions depending on your exact scenario.

For example, as long as all of your AI or Unique identifier field are unique then you can simply alter the table and ensure this is the Primary key and set to unique value.

I solved this with this solution on one of my tables.

The other table had multiple AI int values that were the Primary field, but there were multiple values of the same kind.

The simple fix for this was to just add a column to the end of the table as Unique AI Int. Basically all MySQL is saying is it needs a unique value in each record to differentiate the rows.

Hope this was helpful.

Document level read permissions junction table?

I need to control at the document level what the user type of SubClient sees in a list when they log in.

I have tables userTable and docTable. The app is just a team document app. Manager logs in and sets up a Client and under that client are associated documents and subclients of any number but generally both are around 4 or 5. Clients, subClients and docs comprise a group. One to Many for clients=>subclients and clients=>documents. Unidirectional.

I have this UI to control what PDFs a 'SubClient' sees when they log in. From a database standpoint the best way to handle document level read permissions is a simple userDocJunctionTablewith userID, DocID, AccessRightCodefields.

I feel like I'm not thinking of something and every example I find is overly complicated for this app, like databaseanswers.org/.../document_management_for_security.

JIC it's a Yii2 app with DB based RBAC auth.

Trying to capture value of a button and use ajax to submit a query

I have two buttons in a form. The update button updates the data and works fine. I am trying to implement a delete button that will delete the record based on which ever number is echoed in the button attribute value. Right now it is not doing anything.

<form id="update_form" action="" method="post">
<button id="update" type="submit" value="update">Update</button>   
<button id="unprocess" type="button" value="'.$order_no.'">DEL</button>  
</form>

My Jquery

$("#unprocess").click(function (e) {
         e.preventDefault();
         $.ajax({
             url: 'unprocess.php',
             data: $(this).val(value),
             type: 'POST',
             success: function (data) {
                 $(".error").css('display','none');
                 $(".success").css('display','block');
                 $('#search').focus();
             },
             error: function(data){
                 $(".success").css('display','none');
                 $(".error").css('display','block');
             }

         });

     });

My Query:

$order = $_POST['value'];
    $sql = "DELETE FROM table WHERE ord_no = $order";
    $query = odbc_exec($conn, $sql);

I made the changes on line data:$(this).val(value), now the browser is at least capturing the value of the button, but it is not being passed to the query. any ideas

Logstash: MySQL to Elasticsearch (large table)?

I am attempting to import a rather chunky database into Elasticsearch. It has 4m rows across 2 columns (VARCHAR(250) & INT(20)).

When I run the logstash.conf file to import the database into Elasticsearch and I add a LIMIT 0,100 in my SQL command the command runs without any problems. All of the rows end up returned by Logstash in Terminal and then I can see them in the relevant node in Elasticsearch.

When I try to run all of the rows through Logstash at once, it outputs:

Settings: Default pipeline workers: 1 Pipeline Main Started

And nothing more happens.

How do I add such a large table into Elasticsearch?

Here's my logstash.conf script:

input{

  jdbc {
jdbc_driver_library => "/opt/logstash/mysql-connector-java-5.1.39/mysql-connector-java-5.1.39-bin.jar"

jdbc_driver_class => "com.mysql.jdbc.Driver"

jdbc_connection_string => "jdbc:mysql://<ip number>:3306/database"

jdbc_validate_connection => true

jdbc_user => "elastic"

jdbc_password => "password"

schedule => "* * * * *"

    statement => "name, id from master_table"

    }

}

 output

 {
  elasticsearch
    {

    index => "search"
    document_type => "name"
    document_id => "%{id}"
    hosts => "127.0.0.1:9200"
}stdout { codec => json_lines }
}

QueryforList result show table name

I use springjdbctemplate to query data from hive database.

This is my code:

@RestController
public class QueryController {
    @Autowired
    protected JdbcTemplate hiveTemplate;

    @RequestMapping("/query")
    public List query() {
        List data = hiveTemplate.queryForList("select * from ecg.hivetbluserdata limit 100");
        return data;
    }
}

But it returns this:

[{"hivetbluserdata.id_data":1,"hivetbluserdata.id_user":1,"hivetbluserdata.time":"Thursday, April 09, 2015 18:09:26","hivetbluserdata.ecgvalue":3.3871,"hivetbluserdata.inputtime":"2015-04-09 18:11:25.0"},
{"hivetbluserdata.id_data":2,"hivetbluserdata.id_user":1,"hivetbluserdata.time":"Thursday, April 09, 2015 18:09:26","hivetbluserdata.ecgvalue":1.56892,"hivetbluserdata.inputtime":"2015-04-09 18:11:25.0"},
{"hivetbluserdata.id_data":3,"hivetbluserdata.id_user":1,"hivetbluserdata.time":"Thursday, April 09, 2015 18:09:26","hivetbluserdata.ecgvalue":1.60802,"hivetbluserdata.inputtime":"2015-04-09 18:11:26.0"},
{"hivetbluserdata.id_data":4,"hivetbluserdata.id_user":1,"hivetbluserdata.time":"Thursday, April 09, 2015 18:09:26","hivetbluserdata.ecgvalue":2.09677,"hivetbluserdata.inputtime":"2015-04-09 18:11:26.0"},
{"hivetbluserdata.id_data":5,"hivetbluserdata.id_user":1,"hivetbluserdata.time":"Thursday, April 09, 2015 18:09:26","hivetbluserdata.ecgvalue":1.99902,"hivetbluserdata.inputtime":"2015-04-09 18:11:26.0"},
{"hivetbluserdata.id_data":6,"hivetbluserdata.id_user":1,"hivetbluserdata.time":"Thursday, April 09, 2015 18:09:26","hivetbluserdata.ecgvalue":1.97947,"hivetbluserdata.inputtime":"2015-04-09 18:11:26.0"},
{"hivetbluserdata.id_data":7,"hivetbluserdata.id_user":1,"hivetbluserdata.time":"Thursday, April 09, 2015 18:09:26","hivetbluserdata.ecgvalue":1.94037,"hivetbluserdata.inputtime":"2015-04-09 18:11:26.0"},
{"hivetbluserdata.id_data":8,"hivetbluserdata.id_user":1,"hivetbluserdata.time":"Thursday, April 09, 2015 18:09:27","hivetbluserdata.ecgvalue":1.56892,"hivetbluserdata.inputtime":"2015-04-09 18:11:26.0"},

I don't want to show the table name (hivetbluserdata is the table name). How can I solve this problem?

EF MVC5 Save in different databases

I want to be able to commit new entries in my application's resident database and a similar table in another database with different connection string. I am using Identity2 and Mysql database

I have added the connection string to this new database in web.config But I am not sure how to call and add to other database. Below is what I have tried. Any help will be appreciated

 public class MemberController : Controller
    {
        private DB1 db = new DB1();
        private Db2 db1 = new Db2();

          [HttpPost]
            public ActionResult Create(Member member)
            {
                if (ModelState.IsValid)
                {
                    db1.Customers.Add(member);
                    db.Members.Add(member);
                    int year = Convert.ToInt32(Request["DOB"]);
                    int month = Convert.ToInt32(Request["MOB"]);
                    int day = Convert.ToInt32(Request["BDay"]);
                     if(year>0&&month>0&&day>0){
                         DateTime dateb = new DateTime(year, month, day);

                         member.DateOBirth = dateb;
                     }
                    member.AddDate = DateTime.Now;
                    member.UpDated = DateTime.Now;
                    db.SaveChanges();
                    db2.SaveChanges();


                return RedirectToAction("Index");
            }
}

Error I get is Error 22 'Your.Models.CoDb' does not contain a definition for 'Customers' and no extension method 'Customers' accepting a first argument of type 'Your.Models.CoDb' could be found (are you missing a using directive or an assembly reference?)

SQL returns only some of the results for a complex one to many query

My table structure is as follows

Endoscopy:

PK:Endoscopy_Id
FK:PatientId

This is many to one with...

Patient

PK:Patient_Id

This is one to many with Endoscopy and with Histology

Histology

PK:Histology_Id
FK:PatientId

This is one to many with

HistologyDetails

PK:HistologyDet_Id
FK:Histology_Id

I am trying to perform a query which will extract all the 'astroscopies' and some fields from histology and histologydetails for all the patients. At the moment I am not getting all the results and I am not sure why. I am using:

SELECT 
    Endoscopy.*, 
    Histology.Diagnosis, Histology.NatureOfSpec, 
    Histology.Histology, 
    HistolDetails.MeasurementLargest, HistolDeatils.NumberBx 
FROM 
    Endoscopy  
JOIN 
    PatientData ON Endoscopy.HospNum_Id = PatientData.HospNum_Id   
JOIN 
    Histology ON Histology.HospNum_Id = PatientData.HospNum_Id  
JOIN
    HistolDetails ON Histology.Histology_Id = HistolDetails.Histology_Id
WHERE 
    histology.VisitDate = endoscopy.VisitDate 
    AND (ERFINDINGSSTR LIKE '%Barret%'OR ERDIAGNOSISSTR LIKE '%Barret%') 
    AND Endoscopy.ERPROCEDUREPERFORMED LIKE '%astroscopy%'

PDO prepared insert not throwing error but error persists

When I run this there are no errors thrown but also no data gets stored. I have confirmed that the connection is working (I can select rows from tables).

$host = 'localhost';
$db   = 'property';
$user = 'root';
$pass = '1234';
$charset = 'utf8';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$opt = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION
];
$pdo = new PDO($dsn, $user, $pass, $opt);

$stmt = $pdo->prepare("INSERT INTO Properties (AccountNumber, LegalDescription, PhysicalAddress, SaleStatus) VALUES (:AccountNumber, :LegalDescription, :PhysicalAddress, :SaleStatus)");
$stmt->bindParam(':AccountNumber', '1234');
$stmt->bindParam(':LegalDescription', 'BLOCK 6 LOT 7');
$stmt->bindParam(':PhysicalAddress', '0 FOURTH AVE');
$stmt->bindParam(':SaleStatus', 'PENDING');

if ($stmt->execute()) { 
    echo 'Success!<br />';
} else {
   echo $stmt->error;
}

What other logging can I add to figure out why this is failing? I have scattered line number echo statements throughout to see where the process stops and as far as I can tell, it does not get past the line "$stmt = $pdo->prepare"...never makes it as far as the first bindParam line.

SQL - Count Total Number of Orders By Salesperson

I'm a computer science student working on a homework assignment. I am attempting to total the number of orders for each salesperson. I want my final output to display the salesperson's name along with the number of orders he/she is given credit for.

The information is stored in two separate tables.

Sample data.

Table 1 - order_t

OrderID CustomerID  OrderDate   FulfillmentDate SalespersonID   ShipAdrsID
1   4   2014-09-08  2014-11-25  3   NULL
2   3   2014-10-04  0000-00-00  3   NULL
3   1   2014-07-19  0000-00-00  2   NULL
4   6   2014-11-01  0000-00-00  5   NULL
5   4   2014-07-28  0000-00-00  3   NULL
6   4   2014-08-27  0000-00-00  3   NULL
7   1   2014-09-16  0000-00-00  2   NULL
8   4   2014-09-16  0000-00-00  3   NULL
9   6   2014-09-16  0000-00-00  5   NULL

Table 2 - salesperson_t

SalespersonID   SalespersonName 
1   "Doug Henny"    
2   "Robert Lewis"  
3   "William Strong"    
4   "Julie Dawson"  

Here is my latest query. The problem is it only returns data for a single salesperson that has the ID of 2. I have no idea why this is happening.

SELECT SalespersonName, COUNT(DISTINCT order_t.SalespersonID) AS TotalOrderQuantity
FROM salesperson_t, order_t
WHERE salesperson_t.SalespersonID = order_t.SalespersonID;

Thanks in advance for any help. It is much appreciated!

Getting the same element from database to AmCharts using Primefaces and Javascript

Good day.

Task:

Output diagram using data from DB via AmCharts. Backend: Primefaces + MySQL

I transfer data to AmCharts using this code:

<script type="text/javascript">
    var chart;

    var data = [];

    for (var i = 0; i &lt; '${countryMB.list.size()}'; i++) {
        data.push(
                {
                    country: '${countryMB.list.get(i).getName()}',
                    gdp: '${countryMB.list.get(i).getGdp()}'
                }
        );
    }

    var chartData = data;

    AmCharts.ready(function () {
        // SERIAL CHART
        chart = new AmCharts.AmSerialChart();
        chart.dataProvider = chartData;
        chart.categoryField = "country";
        chart.startDuration = 1;

        // AXES
        // category
        var categoryAxis = chart.categoryAxis;
        categoryAxis.labelRotation = 90;
        categoryAxis.gridPosition = "start";

        // GRAPH
        var graph = new AmCharts.AmGraph();
        graph.valueField = "gdp";
        graph.balloonText = "[[category]]: [[value]]";
        graph.type = "column";
        graph.lineAlpha = 0;
        graph.fillAlphas = 0.8;
        chart.addGraph(graph);

        chart.write("chartdiv");
    });
</script>

countryMB - primefaces component and list - method that gets all elements from database.

And as a result I have data only for one element, that repeats many times. Like this:

I don't understand the reason. Thanks for Your help.

How to store a data whose type can be numeric, date or string in mysql

We're developing a monitoring system. In our system values are reported by agents running on different servers. This observations reported can be values like:

• A numeric value. e.g. "CPU USAGE" = 55. Meaning 55% of the CPU is in use).
• Certain event was fired. e.g. "Backup completed".
• Status: e.g. SQL Server is offline.

We want to store this observations (which are not know in advance and will be added dynamically to the system without recompiling).

We are considering adding different columns to the observations table like this:

IntMeasure -> INTEGER
FloatMeasure -> FLOAT
Status -> varchar(255)

So if the value we whish to store is a number we can use IntMeasure or FloatMeasure according to the type. If the value is a status we can store the status literal string (or a status id if we decide to add a Statuses(id, name) table).

We suppose it's possible to have a more correct design but would probably become to slow and dark due to joins and dynamic table names depending on types? How would a join work if we can't specify the tables in advance in the query?

Inserting values with if and else statements

im making a WF where i get to insert soccer results, however the team winning should get 3, the losing team 0, if its a draw both should get 0.

i have a DB called voetbaldb, with a table called teams, where the scores of the teams should be updated, with 3 columns called ID which is primary key, teamname and teamrank which is unique.

however, i add results with my windows from into my 'results' table, but i want to add points to the 'team' table.

this is my code, however i just cant get it work, i cant find away to give the winnning team the points it deserves:

how to insert data into table based on conditions on the other table

  int value;

        if (int.TryParse(txt_box_home_score.Text > txt_box_away_score.Text, out value))
        {
            UPDATE teams SET teamrank = (SELECT ISNULL(MAX(teamrank), 0) + 3 FROM teams) 
        }
        if else (int.TryParse(txt_box_home_score.Text < txt_box_away_score.Text, out value))
        {
            UPDATE teams SET teamrank = (SELECT ISNULL(MAX(teamrank), 0) + 3 FROM teams) 
        }
        else (int.TryParse(txt_box_home_score.Text == txt_box_away_score.Text, out value))
        {
            UPDATE teams SET teamrank = (SELECT ISNULL(MAX(teamrank), 0) +1 FROM teams)
        }

SQL Join query error in JSF 2.2 with Oracle 11g XE database

Hi everybody I am coding a Library Management System with using JSF 2.2 and Oracle XE 11g database. I have seperate tables like Borrowers Books etc. and certainly some web pages like Borrowers.xhtml and Books.xhtml. In my Borrowers table thee is a foreign key named Bookid. It references to Book table's primary key named Id. They both are Number(5) type attributes. I want to show book names which people borrowed. Now I can only show bookid s. I writed a join sql but this did not work. I have the following in book names line [DataPackage.Book[ isbn=null ]] My codes:

public List<Book> getBookNames(int bookid2)throws ClassNotFoundException,SQLException, InstantiationException, IllegalAccessException{
    Class.forName("oracle.jdbc.driver.OracleDriver");
    con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","Alparslan-PC","123456");

    BookidPs = con.prepareStatement("SELECT DISTINCT TITLE FROM BOOK JOIN BORROWER ON BOOK.ISBN= "+bookid2);
    ResultSet Rs2 = BookidPs.executeQuery();
    List<Book> liste = new ArrayList<Book>();
    while(Rs2.next()){
        book.setTitle(Rs2.getString("TITLE"));
        liste.add(book);
    }
    System.out.print(liste);
    return liste;
}

I tried to turn list to string. (deleted list and wrote String instead of it) I tried everything but this code gives me that error I have written on the top. Please help me. Thanks.

SimpleDateFormat doesn't give me the pattern I need

I'm having a problem with SimpleDateFormat. I have inputfields in my HTML-page with type = date. I want to save these values into my database where they should be saved as dates. I already figured out how to do this and it works. The only probem I encouter is the way the dates are represented in the database.

I want them to be representated as dd-MM-yyyy. Let's say I want to display 01-06-2016 into my database. When doing this with the code I have..it gives me 0006-12-07. Strangely..when I change my pattern into yyyy-MM-dd...it does give me exactly what the pattern says: 2016-06-01. But it doesn't work the other way round.

String parameter = request.getParameter("instroomdatum");
SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");
try {
    Date parsed = sdf.parse(parameter);
    java.sql.Date sql = new java.sql.Date(parsed.getTime());
    student.setInstroomdatum(sql);
} catch (ParseException ex) {
    Logger.getLogger(StudentController.class.getName()).log(Level.SEVERE, null, ex);
}

Can somebody please explain me what's going on and how to fix this problem? It's really annoying.

EDIT: I tried printing the value of parameter with a simple PrintWriter-object for the specific date 01-06-2016. It shows me 2016-06-01, even though my pattern in the code above is set to dd-MM-yyyy.

SELECT * from simple table with 50k records seems to take 40 seconds

Here's my table:

CREATE TABLE public.logs (
  logid bigint NOT NULL DEFAULT nextval('applog_logid_seq'::regclass),
  applicationname character varying(50),
  loglevel character varying(10),
  logmessage character varying(500),
  stacktrace character varying(4096),
  occurredon timestamp without time zone,
  loggedon timestamp without time zone,
  username character varying(50),
  groupname character varying(50),
  useragent character varying(512),
  CONSTRAINT applog_pkey PRIMARY KEY (logid)
);

When I run SELECT *... on it, it takes 40 seconds to return 50000 rows on my local machine. I have the same table on a local install of SQL Server, and that takes less than a second to return the same amount of data.

I'm in the middle of an evaluation of PostgreSQL for our new stack and this is very concerning to me. Why am I doing wrong/why is PostgreSQL so slow?

Edit:

Here's what I get from EXPLAIN (BUFFERS, ANALYZE, TIMING OFF) SELECT * FROM public.logs:

So it looks like the server's going to execute this in about 6 ms. I guess that means all the overhead is in pgAdmin III, but how is SSMS able to do this so much faster?

How to convert rows into columns in laravel

I am totally new in web developing... What I want is this:

The records that i get have three main columns, 'DateTime', 'meter_type', 'value'... Now in here, meter_type have same values i.e. like from 1 to 8 (in image, it is 72 to 79) for different values of DateTime and value... As an example, at DateTime: 2016-06-23 00:01:00, i get 8 different values for all 8 different meter types and similarly at DateTime: 2016-06-23 00:02:00, i will get new set of 8 values for all meter_types...

Now what I want is, to group 'DateTime' column for different 'meter_type' i.e., in this case (above example), I want 9 columns (in total), 1 column for DateTime and remaining all 8 columns depicting 8 meter_type and records for these columns will be value of them... Please refer image for more clarification...

And I want to do this in travel using query builders or eloquent... I googled this, but I didn't found anything useful in travel for what I want... Please help me out here... documentations or properly detailed examples for achieving what I want will really be appreciated.

PostgeSQL select * from simple table with 50000 records takes 40 seconds

Here's my table:

CREATE TABLE public.logs
(
  logid bigint NOT NULL DEFAULT nextval('applog_logid_seq'::regclass),
  applicationname character varying(50),
  loglevel character varying(10),
  logmessage character varying(500),
  stacktrace character varying(4096),
  occurredon timestamp without time zone,
  loggedon timestamp without time zone,
  username character varying(50),
  groupname character varying(50),
  useragent character varying(512),
  CONSTRAINT applog_pkey PRIMARY KEY (logid)
)
WITH (
  OIDS=FALSE
);

When I run SELECT *... on it, it takes 40 seconds to return 50000 rows on my local machine. I have the same table on a local install of SQL Server, and that takes less than a second to return the same amount of data.

I'm in the middle of an evaluation of PostgreSQL for our new stack and this is very concerning to me. Why am I doing wrong/why is PostgreSQL so slow?

Edit:

Here's what I get from EXPLAIN (BUFFERS, ANALYZE, TIMING OFF) SELECT * FROM public.logs:

So it looks like the server's going to execute this in about 6 ms. I guess that means all the overhead is in pgAdmin III, but how is SSMS able to do this so much faster?

Mysql password expired. Can't connect

I just wiped my Mac and did a fresh install of El Capitan. I'm struggling to connect to Mysql now. Having gone through a web server setup process, I've created a simple PHP test file:

<?php
  $conn = new mysqli("127.0.0.1", "root", "xxxxxxxx");
  if ($conn->connect_error) echo "Connection failed: " . $conn->connect_error; 
  else echo "Connected successfully";
  phpinfo();
?>

When I run it, I get this error:

Warning: mysqli::mysqli(): (HY000/1862): Your password has expired. To log in you must change it using a client that supports expired passwords. in /Users/rich/Documents/DESIGN/test/index.php on line 3
Connection failed: Your password has expired. To log in you must change it using a client that supports expired passwords.

I've never seen that response from a connection before. How do I fix it if I can't connect?

EDIT

In terminal I entered the command:

mysql -u root -p

This asked me for my password (current one) which I put in. I now have access to mysql commands but anything I try results in this error:

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

How do I reset the password using ALTER USER?

How do I remove duplicate records

Query:

Select table_c.id_number, table_c.name, table_s.site_name,table_co.Contract_name 
FROM table_c , table_s, table_m, table_o, table_a, table_con 
  WHERE 
   table_s.objid = table_c.sobjid
   AND table_m.cobjid (+) = table_c.objid 
   AND table_o.objid (+) = table_m.olobjid   
   AND table_a.objid (+) = table_o.aobjid 
   AND table_co.objid (+) = table_a.conobjid;

Here I have 6 tables. table_c and table_s have one 2 one relationship. It is possible that for 1 table_o record, we can have 2 table_c records or may be no record.Since I need to take table_co values in SELECT along with table_c and table_s tables, I used left out join on all tables table_c->table_m->table_o->table_a->table_co.

Now on running this query it gives me duplicate records. I have millions of records in table_c so if i use distinct or Union ALL to remove duplicate records, my query takes very long time and this is not acceptable solution.

Can I right this query in such a way that it gives me unique records without performance issue.

Please note, this query is part of a sql view is used by external systems to get data.

Thanks in advance.

PostgreSQL does not accept column alias in WHERE clause

In this pgexercises about joining 3 different tables, the answer is given as following:

select mems.firstname || ' ' || mems.surname as member, 
    facs.name as facility, 
    case 
        when mems.memid = 0 then
            bks.slots*facs.guestcost
        else
            bks.slots*facs.membercost
    end as cost
        from
                cd.members mems                
                inner join cd.bookings bks
                        on mems.memid = bks.memid
                inner join cd.facilities facs
                        on bks.facid = facs.facid
        where
        bks.starttime >= '2012-09-14' and 
        bks.starttime < '2012-09-15' and (
            (mems.memid = 0 and bks.slots*facs.guestcost > 30) or
            (mems.memid != 0 and bks.slots*facs.membercost > 30)
        )
order by cost desc;

Why can't I refer to the cost alias in the SELECT list in the WHERE clause?
If I run the same query with:

        ...
        where
        bks.starttime >= '2012-09-14' and 
        bks.starttime < '2012-09-15' and
        cost > 30
order by cost desc;

an error occurs:

ERROR: column "cost" does not exist

It's clear with me from this answer that it's because of the order of evaluation. But why order by cost desc; is allowed?

Postgresql can not understand alias column

In this pgexercises about joining 3 different tables, the answer is given as following:

select mems.firstname || ' ' || mems.surname as member, 
    facs.name as facility, 
    case 
        when mems.memid = 0 then
            bks.slots*facs.guestcost
        else
            bks.slots*facs.membercost
    end as cost
        from
                cd.members mems                
                inner join cd.bookings bks
                        on mems.memid = bks.memid
                inner join cd.facilities facs
                        on bks.facid = facs.facid
        where
        bks.starttime >= '2012-09-14' and 
        bks.starttime < '2012-09-15' and (
            (mems.memid = 0 and bks.slots*facs.guestcost > 30) or
            (mems.memid != 0 and bks.slots*facs.membercost > 30)
        )
order by cost desc;

What I don't understand is that why can't I refer to the SELECT cost alias at the WHERE clause? If i run the same query with

        where
        bks.starttime >= '2012-09-14' and 
        bks.starttime < '2012-09-15' and
        cost > 30
order by cost desc;

then an error occurs:

ERROR: column "cost" does not exist

It's clear with me from this answer that it's because of the order of evaluation. But why order by cost desc; is allowed?

How to use javascript and HTML to add a sql and button?

I have an application where I want to ADD an AND button that, than creates the option to add an AND statement to the query. This is the php and html code I have to do this at the moment. The problem is I don't know how to connect the php part to the javascript part to create a button that adds exacte the same code?

This the html code:

This is the php code:

<?php

    include "connect.php";

    $table          = $_POST['tableSelected'];
    $field          = $_POST['fieldSelected'];
    $attribute      = $_POST['attributeSelected'];
    $operator       = $_POST['operatorSelected'];
    $fieldList      = $_POST['fieldList'];

    if (!empty($table)){

        if (!empty($fieldList)){
        $fieldstr = $fieldList . ",ST_AsGeoJSON(ST_Transform(l.geom,4326),6)";
        } else {
        $fieldstr = "";   
        }

        $pairs = [];

        foreach ($_POST['fieldSelected'] as $key => $field) {
            if (!empty($field) && !empty($_POST['operatorSelected'][$key]) && !empty($_POST['attributeSelected'][$key])) {
                $pairs[] = $field . " " . $_POST['operatorSelected'][$key] . " '" . $_POST['attributeSelected'][$key] . "'";
            }
        }

        if (count($pairs) > 0) {
            $sql .= ' WHERE ' . implode(' AND ', $pairs);
        }

        //echo ($sql);
?>

And this my html at the moment:

<select name="field[]">...</select>
<select name="operator[]">...</select>
<select name="value[]">...</select>

This is what I want:

Error grouping rows with similar ids together

I am trying to group rows with similar ids (order_id in this case) together. I want each group of similar ids in a separate table. My presence query displays all rows in one table. I don't know how to go about it.

This is my code:

<div class='panel panel-body'>
          <table class='table table-condensed'>
              <thead>
                  <tr class='cart_menu'>
                      <td class='price'>Order ID</td>
                      <td class='price'>Item(s)</td>
                      <td class='price'>Quantity</td>
                  </tr>
              </thead>
              <tbody>
              ";

              $select = "SELECT DISTINCT a.*, b.* FROM shipping_order a JOIN shipping_details b ON b.order_id = a.order_id WHERE user_id = :user_id AND a.order_id = b.order_id";
              foreach ($db->query($select, array('user_id' => $id)) AS $items){

              echo"
              <tr>
                  <td class='cart_price'>
                      <h4>{$items['order_id']}</h4>
                  </td>
                  <td class='cart_price'>
                      <h4><a href=''>{$items['item']}</a></h4>
                      <!-- <p>Web ID: </p> -->
                  </td>
                  <td class='cart_price'>
                      <p>{$items['quantity']}</p>
                  </td>
              </tr>

              ";
              }
              echo"
              {$items['total']}
              </tbody>
          </table>
        </div>

C# wpf Sql Table, encrypted passwords

I am working with a application when user can login in the program by typing their information. Passwords are stored in table as encrypted. But when i try to login using this password it doesn't work.

For encrypting data I use this stored procedure

   ALTER procedure [dbo].[inn]
   @use varchar (50) = null,
   @ins varchar (50) = null

 AS


   INSERT INTO [kole].[dbo].[koll]
       ([Userr]
      ,[ins])


 VALUES
        (@use,
       ((EncryptByPassPhrase('8', @ins)))

And for decrypte I use this stored Procedure

  ALTER procedure [dbo].[sle]
 @Use varchar (50) = null,
 @ins varchar (50) = null

 AS
 SELECT Userr,CONVERT(varchar(50),DECRYPTBYPASSPHRASE ('8',ins)) as Password
FROM [kole].[dbo].[koll]
 where Userr = @Use and ins = @ins

   GO 

In c# I use this code for calling data, and log in into program.

              private void btn_Click(object sender, RoutedEventArgs e)
    {
        SqlConnection conn = new SqlConnection("Server = localhost;Database = kole; Integrated Security = true");

            SqlCommand cmd = new SqlCommand("sle", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Userr", txt.Text);
            cmd.Parameters.AddWithValue("@ins", psw.Password);
            conn.Open();
            SqlDataAdapter adapt = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            adapt.Fill(ds);
            conn.Close();
            int count = ds.Tables[0].Rows.Count;

            if (count == 0)
            {
                MessageBox.Show("This user don't exist");
                SystemSounds.Hand.Play();
                txt.Text = "";
                psw.Password = "";

            }
            else if (count == 1)
            {
                MessageBox.Show("Granted!");
                SystemSounds.Asterisk.Play();
                conn.Open();
    }

How can I decrypt the password in correct way , to use for login into program. Thanks

Easy php issues connecting mysql [duplicate]

This question already has an answer here:

• PDO: MySQL server has gone away 8 answers

my connection php:

class ConnexionBDD{

    public function getConnexion(){

        try {
                $cnx= new PDO("mysql:host=localhost;dbname=portfolio",
                "root",
                "",
                    [   
                        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES 'UTF8'',
                        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
                    ]);
        }


        catch (Exception $e){

        die("Erreur de connexion à la base de donnée : ". $e->getMessage());
        }

        return $cnx;
    }


}

Error code :

Warning: PDO::__construct(): MySQL server has gone away in C:webPortfolioV_3.5controleurConnexionBDD.php on line 15

Warning: PDO::__construct(): Error while reading greeting packet. PID=6980 in C:webPortfolioV_3.5controleurConnexionBDD.php on line 15

Warning: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [2006] MySQL server has gone away' in C:webPortfolioV_3.5controleurConnexionBDD.php:15 Stack trace: #0 C:webPortfolioV_3.5controleurConnexionBDD.php(15): PDO->__construct('mysql:host=loca...', 'root', '', Array) #1 C:webPortfolioV_3.5controleurinsertionBDD.php(10): ConnexionBDD->getConnexion() #2 C:webPortfolioV_3.5controleurContactController.php(15): insertionBDD->createClient('coudoumie', 'morgan', '', '') #3 C:webPortfolioV_3.5index.php(35): ContactController->Contact() #4 {main} thrown in C:webPortfolioV_3.5controleurConnexionBDD.php on line 15

Fatal error: Maximum execution time of 30 seconds exceeded in C:webPortfolioV_3.5controleurConnexionBDD.php on line 15

DBAL cardinality violation error

I am getting the 'Cardinality Violation' error, for the following SQL:

DoctrineDBALExceptionDriverException: An exception occurred while executing

SELECT p.* FROM mod_products_products p 
LEFT JOIN mod_products_products_categories c_link ON c_link.product_id = p.id 
LEFT JOIN mod_products_brands b ON p.brand_id = b.id 
LEFT JOIN mod_products_groups vg ON p.variation_id = vg.id 
LEFT JOIN mod_products_categories c ON c_link.category_id = c.id 
LEFT JOIN mod_products_group_options vg_o ON vg_o.group_id = vg.id 
LEFT JOIN mod_products_group_values vg_o_v ON vg_o_v.option_id = vg_o.id 
WHERE (p.name LIKE (?, ?)) AND (p.parent_id = 0) AND (vg_o.disabled=0) 
GROUP BY p.id ORDER BY p.name ASC 
LIMIT 18446744073709551615 OFFSET 0

with params ["%big%", "%light%"]: SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s).

The error only occurs if there is more than one value defined in the parameter list for WHERE (p.name LIKE (?, ?)).

I am using executeQuery(), and passing the array as Connection::PARAM_STR_ARRAY. In the original statement I am defining the trouble point as:

$builder->andWhere('p.name LIKE (:partial_names)');

It seems it doesn't like getting an array passed as partial_names. Any ideas on what is causing this, and how to avoid it?

Admin panel in php

I am struggling with redirecting to a admin panel page in php. I have tried 2 different codes without success. I have no idea what I am doing, as I don't have a good solid understanding of the fundamentals of php. But I think I'm getting close, any help appreciated. Thankyou in advance.

user_level is the entry in the database , 2 is for members and 3 for admin. error logs posted at the bottom.

  $username = $_SESSION['username'];
            while($row = mysqli_fetch_object($conn))
            {
                $admin = $row->user_level;

        }
        if ($admin == 3) { header('Location: admin.php'); 

    }

code 2

$username = $_SESSION['username'];
$query_level = mysqli_query("SELECT user_level FROM members WHERE username = '$username' ");
$run_level = mysqli_fetch_array($conn, $query_level);
$row = mysqli_fetch_assoc($run_level);
    if ($row == 3) {
        header('Location: admin.php');
    } 

[26-Jun-2016 09:03:09 Australia/Brisbane] PHP Warning: mysqli_fetch_object() expects parameter 1 to be mysqli_result, object given in /public_html/login/memberpage.php on line 46 [26-Jun-2016 09:03:09 Australia/Brisbane] PHP Notice: Undefined variable: admin in /public_html/login/memberpage.php on line 51

26-Jun-2016 09:05:06 Australia/Brisbane] PHP Warning: mysqli_query() expects at least 2 parameters, 1 given in /public_html/login/memberpage.php on line 46 [26-Jun-2016 09:05:06 Australia/Brisbane] PHP Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, null given in /public_html/login/memberpage.php on line 47 [26-Jun-2016 09:05:06 Australia/Brisbane] PHP Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, null given in /public_html/login/memberpage.php on line 48

Fetching two matches in a MySQL query?

So I have some code I'm trying to figure out... I have two tables:

TABLE: matches

event_id
match_id (primary)
match_score
match_p1
match_p2
match_win

TABLE: results

event_id
user_id
result_id (primary)
result_name
result_extra

The weird thing about the data is the content of of the matches table actually links to the results table in multiple fashions.

There will be an integer in match_p1 and match_p2 that link to the results_extra field on the results table. This is designed because each match has two players in it (p1 and p2), and each player has one result for each event.

If I wanted to get a list of all matches in an event, I would do the following:

SELECT *
FROM matches
WHERE event_id = 324

If I wanted to get a list of all matches belonging to a single player, I would do:

SELECT matches.*
FROM matches
    LEFT JOIN results
        ON ((results.result_extra = matches.match_p1) OR
            (results.result_extra = matches.match_p2))
WHERE results.user_id = 1566

However, this is where things get a bit complicated... What if I wanted to get a list of matches where player 1566 fought player 2058? Its the logic for this query I can't figure out. Could you guys help me out?

C# loading images and strings into one DataGridView/ListView using sql query

Hi guys I'm creating this messenger and I would like a List View or DataGridView which shows the users friend's avatar and name. It works perfectly in a list box but I cannot add images.

List box Code:

            var query = from o in Globals.DB.Friends
                    where o.UserEmail == Properties.Settings.Default.Email
                    select new
                    {
                        FirstName = o.FirstName,
                        LastName = o.LastName,
                        Email = o.Email,
                        Display = string.Format("{0} {1} - ({2})", o.FirstName, o.LastName, o.Email)
                    };
        FriendsLb.DataSource = query.ToList();
        FriendsLb.ClearSelected();

List box display and value members:

            FriendsLb.DisplayMember = "Display";
        FriendsLb.ValueMember = "Email";
        FriendsLb.ValueMember = "FirstName";
        FriendsLb.ValueMember = "LastName";

        Globals.DB.Friends.Clear();
        Globals.DB._UserDetails.Clear();
        Globals.DB.AcceptChanges();

        string friends = string.Format("{0}\Friends\" + Properties.Settings.Default.Email + ".dat", Application.StartupPath);
        if (File.Exists(friends))
            Globals.DB.Friends.ReadXml(friends);

        string user = string.Format("{0}\Users\" + Properties.Settings.Default.Email + ".dat", Application.StartupPath);
        if (File.Exists(user))
            Globals.DB._UserDetails.ReadXml(user);

        FriendsLb.ClearSelected();

DataGridView Code:

            var query = from o in Globals.DB.Friends
                    where o.UserEmail == Properties.Settings.Default.Email
                    select new
                    {
                        FirstName = o.FirstName,
                        LastName = o.LastName,
                        Email = o.Email,
                        Avatar = o.Avatar,
                        Display = o.Avatar + string.Format("{0} {1} - ({2})", o.FirstName, o.LastName, o.Email)
                    };
        FriendsDGV.DataSource = query.ToList();

Problem is: DataGridView doesn't provide display and value members which is needed to load the data.

List View: Doesn't provide data source or display and member values

How can I do this? :(

QueryOver - adding additional criteria to a Join

I'm quite new to NHibernate and QueryOver and I can't get NHibernate to generate the SQL I need.

I need to make a join and have an extra criteria on so I avoid getting to much data from the table I'm joining with.

The SQL I receive from QueryOver is:

SELECT * FROM adresse this_ 
left outer join r580_test.afvigelse remarkalia1_ on this_.id=remarkalia1_.adrid 
left outer join r580_test.afvigelseklagepunkter remarkcomp5_ on remarkalia1_.id=remarkcomp5_.afvigelseid 
left outer join r580_test.klagepunkter complainta2_ on remarkcomp5_.klagepunktid=complainta2_.id 
WHERE  this_.id = 16633 and remarkalia1_.dato between '2009-03-13 00:00:00' and '02-03-2012 16:34:35'

What I would like is this(the where date between has been moved to the end for the first left outer join):

SELECT * FROM adresse this_ 
left outer join r580_test.afvigelse remarkalia1_ on this_.id=remarkalia1_.adrid and remarkalia1_.dato between '2009-03-13 00:00:00' and '02-03-2012 16:34:35'
left outer join r580_test.afvigelseklagepunkter remarkcomp5_ on remarkalia1_.id=remarkcomp5_.afvigelseid 
left outer join r580_test.klagepunkter complainta2_ on remarkcomp5_.klagepunktid=complainta2_.id 
WHERE  this_.id = 16633

My QueryOver looks like this:

adr = session.QueryOver<Address>()
    .Where(x => x.Id == 16633)
    .JoinQueryOver<Remark>(y => y.Remarks).Where(y => y.Created > DateTime.Now.AddDays(-14))
    .JoinAlias(y => y.RemarkComplaint, () => complaintAlias, JoinType.LeftOuterJoin)
    .SingleOrDefault();

Anyone got an idea about how to fix this?

Best way to show friends of a user in alphabetical order

On a social network I am working on in PHP/MySQL, I have a friends page, it will show all friends a user has, like most networks do. I have a friend table in MySQL, it only has a few fields. auto_ID, from_user_ID, to_friend_ID, date

I would like to make the friends page have a few different options for sorting the results,

1. By auto_ID which is basically in the order a friend was added. It is just an auto increment id

2. new friends by date, will use the date field

3. By friends name, will have a list in alphabetical order.

The alphabetical is where I need some advice. I will have a list of the alphabet A-Z, when a user clicks on K it will show all the user's name starting with K and so on. The trick is it needs to be fast so doing a JOIN on the user's table is not an option, even though most will argue it is fast, it is not the performance I want for this action. One idea I had is to add an extra field to my friendship table and store the first letter of the users name in it. User's can change there name at anytime so I would have to make sure this is updated on possible thousands of records, anytime a user changes there name.

Is there a better way to do this?

MySQL Transaction returns error

So here's the stored procedure I've written. When I ran the DELETE and UPDATE in a single sql tab

as:

DELETE FROM curriculumsubjects WHERE curriculumId = 27;
INSERT INTO curriculumsubjects(curriculumId,subjectCode)
VALUES(27,'MATH101');

it works. It executes delete and insert without any problem

But if I call the stored procedure as:

CALL `enrollmentdb`.`updateCurriculumSubjects`(27, 'MATH101'); 

it returns the 'error' string i put during ROLLBACK

What could be causing the failure of transaction within the stored procedure body when it runs successfully if ran without stored procedure CALL?

Here's the stored procedure.

CREATE DEFINER=`root`@`localhost` PROCEDURE `updateCurriculumSubjects`(IN p_curriculumId int, IN p_subjectCode varchar(100))
BEGIN
    DECLARE hasError BOOLEAN DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR sqlexception SET hasError = 1;

    START TRANSACTION;
        DELETE FROM curriculumsubjects WHERE curriculumId = p_curriculumId;
        INSERT INTO curriculumsubjects(curriculumId,subjectCode)
                                VALUES(p_curriculumId,p_subjCode);
    IF hasError THEN
        ROLLBACK;
        SELECT 'error';
    ELSE
        COMMIT;

    END IF;
END

By the way I'm using Mysql Workbench 6.3 and what I'm trying to do is to delete all the columns matching the curriculumId before I insert again.

On Java, I'll be iterating the call to the stored procedure for multiple inserts.

I hope you can help. I just can't find a reason why delete and insert won't work if put within a transaction.

Thanks.

Ratio or Percentage from group by SQL query from column with condition and without condition

I am having some trouble with a SQL query. From a table let's call it Reports:

I want to group all the reports by the name column.

Then for each of those name groups I want to go to the rating column and count the number of times the rating was 15 or less. Let's say this happened 10 times for one of the groups with the name BOBBO.

I also want to know the number of times ratings were submitted (same as total number of records for each name group). So using the name group BOBBO let's say he has 20 ratings.

So under the condition the group BOBBO 50% of the time has a rating 15 or less.

I've seen these posts -- I am still having some trouble cracking this.

using-count-and-return-percentage-against-sum-of-records

getting-two-counts-and-then-dividing-them

getting-a-percentage-from-mysql-with-a-group-by-condition-and-precision

divide-two-counts-from-one-select

After reading those I tried queries like these:

    ActiveRecord::Base.connection.execute
("SELECT COUNT(*) Matched,
     (select COUNT(rating) from reports group by name) Total,
     CAST(COUNT(*) AS FLOAT)/CAST((SELECT COUNT(*) FROM reports group by name) AS FLOAT)*100 Percentage from reports
    where rating <= 15 order by Percentage")


    ActiveRecord::Base.connection.execute
("select name, sum(rating) / count(rating) as bad_rating
 from reports group by name having bad_rating <= 15")

Any help would be very much appreciated!

Using Case When to Classify Customers in Teradata

I am researching customers and their shopping habits using SQL in Teradata.

Say I have the following information:

Table wklytrans
CustNm  GroceryStore  # WklyTrxns  Sum WklyTrxns  CustType
Jack    Publix        2            $70            Publix
Ruby    Kroger        1            $15            Both
Moe     Kroger        4            $120           Kroger
Ruby    Publix        1            $20            Both

I would like to create a new table that classifies them as follows:

CustNm  CustType  # PubTrxns  Sum PubTrxns  # KroTrxns  Sum KroTrxns
Jack    Publix    2           $70           NULL        NULL
Ruby    Both      1           $20           1           $15
Moe     Kroger    NULL        NULL          4           $120

The code I'm currently trying to run is as follows:

CREATE MULTISET VOLATILE TABLE custclass AS(
SEL
CustNm,
CustType,
CASE WHEN CustType IN 'Both' AND GroceryStore IN 'Publix' THEN # WklyTrxns
WHEN CustType IN 'Publix' THEN # WklyTrxns
END AS # PubTrxns,
CASE WHEN CustType IN 'Both' AND GroceryStore IN 'Publix' THEN Sum WklyTrxns
WHEN CustType IN 'Publix' THEN Sum WklyTrxns
END AS Sum PubTrxns,
CASE WHEN CustType IN 'Both' AND GroceryStore IN 'Kroger' THEN # WklyTrxns
WHEN CustType IN 'Kroger' THEN # WklyTrxns
END AS # KroTrxns,
CASE WHEN CustType IN 'Both' AND GroceryStore IN 'Kroger' THEN Sum WklyTrxns
WHEN CustType IN 'Kroger' THEN Sum WklyTrxns
END AS Sum KroTrxns
FROM wklytrans
)
WITH DATA                                      
PRIMARY INDEX (CustNm)                          
ON COMMIT PRESERVE ROWS;

This is what is currently being outputted:

CustNm  CustType  # PubTrxns  Sum PubTrxns  # KroTrxns  Sum KroTrxns
Jack    Publix    2           $70           NULL        NULL
Ruby    Both      1           $20           NULL        NULL
Ruby    Both      NULL        NULL          1           $15
Moe     Kroger    NULL        NULL          4           $120

How can I get my code to combine Ruby's transactions onto the same line so that it looks like the output I desire?

Any help is appreciated!

GAE Python - OperationalError: (2013, 'Lost connection to MySQL server during query')

I've been trying to connect to ClouSQL using Flexible Environments (vm:true)

but when I upload my app using:

gcloud preview app deploy --version MYVERSION

An error is thrown:

OperationalError: (2013, 'Lost connection to MySQL server during query')

I found out that it might be because the query is too large but I think that's not the case because it works locally and on production when I wans't using flexible environments with MySQLdb.

My code:

import os
import logging
import pymysql

class MySQL(object):
    '''
    classdocs
    '''
    # TO INSTALL LOCAL DB: http://stackoverflow.com/questions/30893734/no-module-named-mysql-google-app-engine-django


    @classmethod
    def getConnection(cls):
        # When running on Google App Engine, use the special unix socket
        # to connect to Cloud SQL.
        if os.getenv('SERVER_SOFTWARE', '').startswith('Google App Engine/'):
            logging.debug('PROJECT [%s], INSTANCE[%s] - USER [%s] - PASS [%s], SCHEMA [%s]',
                          os.getenv('CLOUDSQL_PROJECT'),
                          os.getenv('CLOUDSQL_INSTANCE'),
                          os.getenv('CLOUDSQL_USER'),
                          os.getenv('CLOUDSQL_PASS'),
                          os.getenv('CLOUDSQL_SCHEMA'))

            db = pymysql.connect(unix_socket='/cloudsql/APP:REGION:INSTANCENAME')
                    #os.getenv('CLOUDSQL_PROJECT'),
                    #os.getenv('CLOUDSQL_INSTANCE')), 
                    #user=os.getenv('CLOUDSQL_USER'),
                    #passwd=os.getenv('CLOUDSQL_PASS'),  
                    #db=os.getenv('CLOUDSQL_SCHEMA'))
        # When running locally, you can either connect to a local running
        # MySQL instance, or connect to your Cloud SQL instance over TCP.
        else:
            db = pymysql.connect(host=os.getenv('DBDEV_HOST'), user=os.getenv('DBDEV_USER'), 
                                 passwd=os.getenv('DBDEV_PASS', ''), db=os.getenv('DBDEV_SCHEMA'))

        return db

Any thoughts on this?

Thanks!

"set names" vs mysqli_set_charset — besides affecting mysqli_escape_string, are they identical?

It seems to be common knowledge to use mysql_set_charset / mysqli::set_charset instead of the direct MySQL query set names.

The reason often cited is that set names is insecure because the encoding used for mysql_real_escape_string / mysqli::real_escape_string will only be set by a call to mysql_set_charset / mysqli::set_charset. (Another reason cited is that the PHP docs says it's "not recommended" §.)

However, is it safe to use the direct MySQL query set names if we use prepared statements andor other means of escaping besides mysql_real_escape_string / mysqli::real_escape_string / mysqli_escape_string?

Besides affecting the encoding of mysql_real_escape_string / mysqli::real_escape_string / mysqli_escape_string, Is there any difference between set names vs mysql_set_charset/mysqli::set_charset?

Undefined text in lines 6,9 and 10. Why am I getting this error? [duplicate]

This question already has an answer here:

• PHP: “Notice: Undefined variable” and “Notice: Undefined index” 13 answers

t1 has username,

t2 has password.

The page from which this is redirected has another field t3, which compares t2 and t3.

This error started appearing only after adding this t2 and t3 comparison

removal of the t3 check makes this work again.

<?php
$con = mysqli_connect("localhost","root","","company");
if(mysqli_connect_errno()){
    echo "Could not connect.";
}
$query = "SELECT * FROM login_info WHERE suser = '{$_POST['t1']}'"; //line 6
$queryArray = mysqli_fetch_array(mysqli_query($con,$query));
if(empty($queryArray['suser'])){
    echo "<h2 style='font-size: 40px;'><br><br><br><br><center><font face=Papyrus color=#425A87 style='text-shadow: 0px 0px 7px white;'>Welcome, user ".$_POST['t1'].". This is your first login to this website.</font></center></h2>"; //line 9
    mysqli_query($con,"INSERT INTO login_info VALUES ('{$_POST['t1']}','{$_POST['t2']}')");  //line 10
} 
else{
    echo "<h2 style='font-size: 40px;'><br><br><br><br><center><font face=Papyrus color=#425A87 style='text-shadow: 0px 0px 7px white;'>Welcome back ".$_POST['t1']."</font></center></h2>";
}
mysqli_close($con);     
require_once 'display.php';
?>

how to get similar search results in MySQL?

I am trying to search by words similarity or even if someone made a typo.

For example if anyone searches with picadilly correct one is piccadilly, It should get the result.

I had tried by searching by removing a char from last until 3 chars left using PHP, for example picadilly picadill picadil picadi picad pica pic

It's working but I am also getting unwanted results (due to shorten of keyword to 3 chars) which aren't similar, Also don't think it is a good way, Is there any better approach ?

Table Structure & Data:

+----+---------------------------+------------------------------------------------------+
| id | name                      | address                                              |
+----+---------------------------+------------------------------------------------------+
|  1 | Bethnal Green Station     | Bethnal Green Tube Station, London, E2 0ET           |
|  2 | Westminster Station       | Westminster Tube Station, London, SW1A 2JR           |
|  3 | Goldhawk Road Station     | Goldhawk Road Tube Station, London, W12 8EG          |
|  4 | Piccadilly Circus Station | Piccadilly Circus Tube Station, London, W1J 9HP      |
|  5 | Ravenscourt Park Station  | Ravenscourt Park Tube Station, London, W6 0JJ        |
|  6 | Barons Court Station      | Barons Court Tube Station, London, W14 9EA           |
|  7 | Charing Cross Station     | Charing Cross Tube & Train Station, London, WC2N 6RQ |
|  8 | Hammersmith Station       | Hammersmith Tube Station, London, W6 8AB             |
|  9 | Embankment Station        | Embankment Tube Station, London, WC2N 6NS            |
| 10 | Leicester Square Station  | Leicester Square Tube Station, London, WC2H 0AP      |
+----+---------------------------+------------------------------------------------------+

Query:

SELECT * FROM `stations`
WHERE name like '%picadilly%'
   OR name like '%picadill%'
   OR name like '%picadil%'
   OR name like '%picadi%'
   OR name like '%picad%'
   OR name like '%pica%'
   OR name like '%pic%'