Solved

insert query not working on server only on localhost

Posted on 2010-09-20
8
337 Views
Last Modified: 2012-05-10
Hi,

I have a insert query that does not seem to work.

 

I am creating a entry into matches to get match_id and then use match_id to populate the rest of the info into the other table.

 

It works on localhost, but not on my server. It gives me this:
Warning:  mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/site/create-fixtures.php on line 39

line 39 is: while($match = mysql_fetch_assoc($result))
$season_id = date('Y');

 

// Get the list of matches
$result = mysql_query('SELECT h.team_id as hometeam, a.team_id as awayteam FROM team h, team a WHERE h.team_id <> a.team_id');
while($match = mysql_fetch_assoc($result))
{
  // Create a match record and 2 matchscore records
  mysql_query("insert into matches set season_id = $season_id");
  $match_id = mysql_insert_id();
  mysql_query("insert into matchscores set match_id=$match_id, team={$match['hometeam']}");
  mysql_query("insert into matchscores set match_id=$match_id, team={$match['awayteam']}");
}

Open in new window

0
Comment
Question by:cataleptic_state
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 

Expert Comment

by:orsobruno
ID: 33714613
try to correct the query

SELECT h.team_id as hometeam, a.team_id as awayteam FROM team AS h, team AS a WHERE h.team_id <> a.team_id
0
 
LVL 2

Expert Comment

by:rameshfromind
ID: 33714852
I think this query will return the null value thats why getting error if there is any value available only control enter the while loop.

1. There is no data in server database thourgh the condition[h.team_id <> a.team_id].
2. But you have  the data in local server.

'SELECT h.team_id as hometeam, a.team_id as awayteam FROM team h, team a WHERE h.team_id <> a.team_id'

If you need any assistance let me know.
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33716058
Hi,
the team information is in the server database.

Also on some of the other pages it says "No database selected"
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 4

Expert Comment

by:chrisbloom7
ID: 33724490
When you are running locally, are you also connecting to a local database? And then when you run it on the remote server, is the database local to the webserver? And are you updating your config file or database connection values to use the proper credentials in each environment?

You could also check to see if either the connection string is wrong once you push the code to the remote server, or the database doesn't have the proper permissions to allow connections from the remote server.
0
 
LVL 5

Expert Comment

by:innotionent
ID: 33724703
Is that all your code?
where is your mysql conection command?


$link = @mysql_connect("localhost", "username", "password") or die("<b>Could not connect to DB</b>");
	
$myquerystring = "select * from table where foo = bar";
	
if(!($result = mysql_db_query("database",$myquerystring,$link))){
	print("MySQL reports: " . mysql_error() . "\n");
		exit();
}

Open in new window

0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33724945
Hi,
This is all I have on this page (in the way of mysql):

<?php require_once('Connections/db.php'); ?>
<?php
 include "signInChk.php";
?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$season_id = date('Y');

// Get the list of matches
$result = mysql_query('SELECT h.team_id as hometeam, a.team_id as awayteam FROM team h, team a WHERE h.team_id <> a.team_id');
while($match = mysql_fetch_assoc($result))
{
  // Create a match record and 2 matchscore records
  mysql_query("insert into matches set season_id = $season_id");
  $match_id = mysql_insert_id();
  mysql_query("insert into matchscores set match_id=$match_id, team={$match['hometeam']}");
  mysql_query("insert into matchscores set match_id=$match_id, team={$match['awayteam']}");
}


$maxRows_news = 6;
$pageNum_news = 0;
if (isset($_GET['pageNum_news'])) {
  $pageNum_news = $_GET['pageNum_news'];
}
$startRow_news = $pageNum_news * $maxRows_news;

mysql_select_db($database_db, $db);
$query_news = "SELECT * FROM news ORDER BY news_id DESC";
$query_limit_news = sprintf("%s LIMIT %d, %d", $query_news, $startRow_news, $maxRows_news);
$news = mysql_query($query_limit_news, $db) or die(mysql_error());
$row_news = mysql_fetch_assoc($news);

if (isset($_GET['totalRows_news'])) {
  $totalRows_news = $_GET['totalRows_news'];
} else {
  $all_news = mysql_query($query_news);
  $totalRows_news = mysql_num_rows($all_news);
}
$totalPages_news = ceil($totalRows_news/$maxRows_news)-1;

mysql_select_db($database_db, $db);
$query_enable_login = "SELECT disable_login FROM season";
$enable_login = mysql_query($query_enable_login, $db) or die(mysql_error());
$row_enable_login = mysql_fetch_assoc($enable_login);
$totalRows_enable_login = mysql_num_rows($enable_login);

// Initialize the Alternate Color counter
$ac_sw1 = 0;
?>

Open in new window

0
 
LVL 5

Accepted Solution

by:
innotionent earned 500 total points
ID: 33725034
i see you have the database variables in the code. but where are the actual values in your script?

that no database selected error you are getting means exactly that.

you haven't specified a database for it to query.

$database_db and $db must have values in them somewhere. just make sure that you do.
0
 
LVL 4

Expert Comment

by:chrisbloom7
ID: 33725499
Somewhere in Connections/db.php there should be a line that calls mysql_connect(). If not, then that is part of your problem. If it's there, then you need to make sure you are not getting a connection error remove any "@" sign before the call and refresh the page, then check for any errors displayed on the page or in your PHP error log. If the connection is failing any error message should give you an indication of why. If you feel that you need to post your Connections/db.php file be sure to redact any sensitive information (e.g. username, password, database URL) first.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question